Forum Discussion
converting a general number that includes text into a date/military time format
I can use the formatting and the custom formatting to convert the cells into the date/time format, but the time does not understand the military time because the time is based on AM and PM text at the end of the string: example below
| 2023/11/14 9:45:45 AM EST |
| 2023/11/15 9:32:59 PM EST |
so when I format the fields using the CATEGORY: custom and I enter the TYPE: yyyy/mm/dd hh:mm:ss
the time displayed after the date is always in the military time for AM.
| 2023/11/14 9:45:45 |
| 2023/11/15 9:32:59 |
What formula can I run on the column to change the format from general number to a custom Date/Military Time format and the time correctly displays in military time.
| 2023/11/15 21:32:59 |
I'm wasting hours on this small task. Thanks for your help.
Al
- You can use PowerQuery to do this. If you prefer a formula: on my PC, this simple formula works:
=--LEFT(A2, 22)
Format the cell with the formula as date and time, then fill down. If it fails on your PC:
=DATE(LEFT(A2, 4), MID(A2, 6, 2), MID(A2, 9, 2))+TIMEVALUE(MID(A2, 12, 11))
Again, format as date and time, then fill down.
5 Replies
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- I am attaching a worksheet to help understand the problem.
https://www.dropbox.com/scl/fi/a0kd3t08kx1onjr0285g7/converting-general-number-that-incl-txt-into-a-date-and-military-time-format.xlsx?rlkey=qv8eai0yl36zgozpjr5wrv8sa&dl=0- You can use PowerQuery to do this. If you prefer a formula: on my PC, this simple formula works:
=--LEFT(A2, 22)
Format the cell with the formula as date and time, then fill down. If it fails on your PC:
=DATE(LEFT(A2, 4), MID(A2, 6, 2), MID(A2, 9, 2))+TIMEVALUE(MID(A2, 12, 11))
Again, format as date and time, then fill down.
