Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Elapsed time when separate cells contain time and separate date | New Users to Excel | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Worksheet Functions |