# Change Time into Military

Options

Hi all -

Looking for solution to a simple task. Using a form that shows date in this format: 8:00 AM, 9:30 AM, etc., Want to add a second column (that will be hidden from form) that changes this to military time. No calculations involved - only looking for the time conversion.

Yes, we could ask for military time in our form, but we want it to be quick and easy for our team members and not all are as versed in military time.

Thought about adding both options in the drop down (like 10:00 am/1000) and then parsing it out, but was hoping that someone may have a better suggestion.

Thanks!

Tags:

«13

• edited 01/15/20
Options

I think another potential work-around would be something similar to the solutions in this forum:

https://community.smartsheet.com/discussion/2376/separating-data-in-a-single-column-into-separate-columns

I think this solution would work because if a cell has "4:00 PM" in it, it is being seen as a string by smartsheets. With a nested if statement to determine AM/PM... you may also need to have a hidden column or two in case the function is too difficult to cleanly format in one cell and it needs to be split up.

-Austin

• Options

Thank you everyone! I tried the formula below from L@123 and it works great except for 12:00 pm and 12:30 pm, showing 24:00 and 24:30 respectively instead of 00:00 and 00:30. HOWEVER, I am waiting to see what our other system needs it to read to ingest and will throw that back out on this post.

=(VALUE(LEFT([Sun Open]@row, FIND(":", [Sun Open]@row) - 1)) + IF(RIGHT([Sun Open]@row, 2) = "pm", 12, 0)) + ":" + MID([Sun Open]@row, FIND(":", [Sun Open]@row) + 1, 2)

• Options

It worked! Returned 12:00 and 12:30 - I'm assuming that's what is needed for our system, but if not I'll repost! Thanks again!!

• Options

Big surprise - now I need to add something else :) I needed to drop the ":' in the military time and now need a duration between the two times. For some reason, a simple subtract calculation does not work. Any ideas of why? Is it because the two columns leading up to it are both formulas?

• ✭✭✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

you're right. how many time formulas have I written and I didn't even think of that. Man I am out of it with this one. Thanks @Paul Newcome

• Options

Thank you so much!! I dont know how you guys do it!! I have racked my brain on these things!!

We will never have anything rolling over days (at least not yet), so that is one problem we dont have to worry about. But getting a negative number on anything that starts before noon:

• ✭✭✭✭✭✭
Options

Which solution are you using? Depending on your formula, it may be that there is an assumption that the time will always be 4 digits with a leading zero for AM times before 10.

0800 vs 800 for 8 AM

• Options

@Paul Newcome Ahhh - Good call out. Its because of the 800. Here was my formula to change to military time. (compliments of the awesome L@123)

=VALUE((VALUE(LEFT([Sun Open]1, FIND(":", [Sun Open]1) - 1)) - IF(VALUE(LEFT([Sun Open]1, FIND(":", [Sun Open]1) - 1)) = 12, 12, 0) + IF(RIGHT([Sun Open]1, 2) = "pm", 12, 0)) + MID([Sun Open]1, FIND(":", [Sun Open]1) + 1, 2))

• ✭✭✭✭✭✭
Options

And what is your calculation formula (the one producing the end result of duration)?