Forum Discussion
Solved
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 th...
- 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.
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
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.
=--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.
- That worked.
I formatted the column with the date/military time.
Then ran this formula: =--LEFT(A2, 22)
and worked!
Thanks much