Separate Day and Time from a Date Field

[Bman342]

I receive a batch data feed with a date/time field in excel-compliant format.
I would like to separate the Day (ie, Monday, Tuedsday, etc), and the Time
(8:00AM) into separate columns, and still show the Date in its own column.
I've done a =lookup on a =weekday function to get the day, and that works.
But how do I pull out the time? I've looked at different functions and cannot
figure out how to do this. Any help would be appreciated.

-b

 

[T. Valko]

Try this...

A1 = 12/15/2007 5:35 PM

To get the weekday in long name format (Saturday):

=TEXT(A1,"dddd")

To get the weekday in short name format (Sat):

=TEXT(A1,"ddd")

To get the time:

=MOD(A1,1)

Format as TIME

 

[Bman342]

Never mind. Just a formatting issue.

-b

 

[macropod]

Hi Bman342,

Assuming your date & time are in A1, you can retrieve the time with:
=A1-INT(A1)

However, your simplest solution might be to simply use a formula like =A1 and format your day and time target cells appropriately.
For example, to get the day, give the target cell a custom format of 'dddd'. Similarly, to get the time, give the target cell a
custom format of 'h:mm AM/PM'.

Cheers

 

[Bman342]

Biff:

Thanks! The Mod solution works, as I need to sort on the Time. And could not
by just changing the formatting. Now I can.

-b

 

[T. Valko]

You're welcome. Thanks for the feedback!