I have a long length of times that are formatted XXXX AM or XXXX PM. I need to convert these into military time so I can do a search on them. I tried to format them, but since there is no colon in the time it does not convert. Can anyone help me with this?
Thanks in advance!
Jeff B
Last edited by boynejs; 11-15-2021 at 12:44 PM.
Try
=IF(RIGHT(A1,2)="AM",TIME(LEFT(A1,2),--MID(A1,3,2),0),TIME(LEFT(A1,2)+12,--MID(A1,3,2),0))
format cell as hh:mm
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
I am still having issues with this. I tried the suggested formula...
=IF(RIGHT(A1,2)="AM",TIME(LEFT(A1,2),--MID(A1,3,2),0),TIME(LEFT(A1,2)+12,--MID(A1,3,2),0))
and the times are not correct. Here is an attached spreadsheet of what I am getting. The more I think about it, I would be fine with it just giving me a time in AM or PM.
Jeff B
Try:
=TEXT(LEFT(A2,4),"00\:00 \a\m") + IF(RIGHT(A2,2)="pm",0.5)
Caveat: I assume that midnight is input as either "1200 am" or "000 am", not "2400 pm".
Last edited by curiouscat408; 11-15-2021 at 11:36 AM.
That worked. Thanks
I'm late to the party again, but here's another way.Now convert.Formula:
Please Login or Register to view this content.
Edit: Actually this is simpler than my last.Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 11-15-2021 at 08:00 PM.
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks