I have Office 2007 running on a Windows 7 64 bit Toshiba laptop. I am trying to separate the data and time in an Excel file in one column and put the date in one column and the time in another. I need a "how to" procedure to do that.
Excel has formatting for Date and Time.
For column A use a Date format, and for column B use a Time format.
To copy the value from column A to column B a function in column B like this should work
= IF(A1="","",A1)
I tried that, but all the elements of the date and time are still there and that results in just what is seen in the viewing. In one column I want only the date data, and in the other column only the time data so those two different elements can be used separately in a variable data program.
Yes, formatting only affects the displayed data.
To get the date, add a column with this formula
=IF(A1="","",TEXT(A1,"mm/dd/yyyy"))
To get the time, add a column with this formula (remove the am/pm if you want 24hr fmt)
=IF(A1="","", TEXT(A1,"hh:mm:ss am/pm"))
Last edited by DavidE; 13 Dec 2012 at 14:56. Reason: added if logic
Thanks, DavidW7ncus, that works great on my laptop with Windows 7. I appreciate the help very much. Now if I can also figure out how to do it on a Mac OS X I will have both computers covered.
Hello rcd1947,
You're welcome and thanks for posting back.
I don't have a Mac, so i don't know that can help with that.
What happens on the Mac?
Is the formula invalid, not work correctly, ... ?
If adding a screen print showing the problem is easier/better, this explains how to to this (Windows instructions).
Screenshots and Files - Upload and Post in Seven Forums
Quote