Separating date and time in Excel Office 2007

[rcd1947]

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.

 

[DavidE]

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)

 

[rcd1947]

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.

 

[DavidE]

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"))

 

[rcd1947]

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.

 

[DavidE]

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).
http://www.sevenforums.com/tutorials/9733-screenshots-files-upload-post-seven-forums.html