Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format serial date and time
I have been trying to format a serial date to the format yyyy/mm/dd 00;00 am
An example of the text is 200501090932. The time is in military time. I have tried the text ot columns but my string is too long. Any help will be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format serial date and time
The serial date in EXCEL for the string you gave is 38361.40.
I converted it using the following equation =DATE(LEFT(A1,4),MONTH(MID(A1,5,2)),DAY(MID(A1,7,2 )))+TIME(MID(A1,9,2),MID(A1,11,2),) where A1 contained the value 200501090932. "Cedric" wrote in message ... I have been trying to format a serial date to the format yyyy/mm/dd 00;00 am An example of the text is 200501090932. The time is in military time. I have tried the text ot columns but my string is too long. Any help will be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format serial date and time
Another way
=--TEXT(TEXT(LEFT(A1,8),"0000\-00\-00"),"dd/mm/yyyy")--TEXT(TEXT(RIGHT(A1,4) ,"00\:00"),"hh:mm") -- HTH RP (remove nothere from the email address if mailing direct) "Barb Reinhardt" wrote in message ... The serial date in EXCEL for the string you gave is 38361.40. I converted it using the following equation =DATE(LEFT(A1,4),MONTH(MID(A1,5,2)),DAY(MID(A1,7,2 )))+TIME(MID(A1,9,2),MID(A 1,11,2),) where A1 contained the value 200501090932. "Cedric" wrote in message ... I have been trying to format a serial date to the format yyyy/mm/dd 00;00 am An example of the text is 200501090932. The time is in military time. I have tried the text ot columns but my string is too long. Any help will be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Date & Time format | Excel Worksheet Functions | |||
Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions | |||
how do I format cells to change date and time to just date | Excel Discussion (Misc queries) | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions | |||
How do I format column containing date and time to only show the . | Excel Discussion (Misc queries) |