Forum Discussion

Copper Contributor

Jan 16, 2024

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...

excel

Formulas and Functions

Hans_Vogelaar
Jan 17, 2024
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.

HansVogelaar

MVP

Jan 16, 2024

ahha_ha

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?

ahha_ha

Copper Contributor

Jan 17, 2024

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

Hans_Vogelaar
Copper Contributor
Jan 17, 2024
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.
- ahha_ha
  Copper Contributor
  Jan 17, 2024
  That worked.
  I formatted the column with the date/military time.
  Then ran this formula: =--LEFT(A2, 22)
  and worked!
  
  Thanks much
ahha_ha
Copper Contributor
Jan 17, 2024
I've tried this formula:
=SUBSTITUTE(SUBSTITUTE(TEXT(A2,"yyyy/mm/dd hh:mm:ss")," AM","")," PM","")+0
but I get an error: #value!

I tried this formula:
=TEXT(A2,"yyyy/mm/dd ")&IF(RIGHT(A2,2)="AM",TIMEVALUE(LEFT(A2,LEN(A2)-3)),TIMEVALUE(LEFT(A2,LEN(A2)-3))+TIME(12,0,0))

Here is the result, but it still does not add to the hours (hh) making 4 pm military time 16 (using the 24 hour clock) and it adds many numbers afterwards:
2024/01/02 4:24:38 PM EST1.18377314815007