Separate Day and Time from a Date Field
 

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

Separate Day and Time from a Date Field
 

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

--
Biff
Microsoft Excel MVP


"Bman342" wrote in message
...
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

Separate Day and Time from a Date Field
 

Never mind. Just a formatting issue.

-b


"Bman342" wrote:

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

Separate Day and Time from a Date Field
 

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
--
macropod
[MVP - Microsoft Word]
-------------------------

"Bman342" wrote in message ...
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

Separate Day and Time from a Date Field
 

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" wrote:

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

--
Biff
Microsoft Excel MVP


"Bman342" wrote in message
...
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

Separate Day and Time from a Date Field
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bman342" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"Bman342" wrote in message
...
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