Change Time into Military

Cheryl Petrich

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!

L_123

=(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)

Give that formula a try.

L_123

Whoops. I always forget about the 12's. Give this a try.

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

L_123

This will get rid of the colon, and should convert it to a number format so you can simply subtract the two cells.

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

Paul Newcome

@L@123 Unfortunately simply subtracting the two cells won't work as it will subtract the minutes based on 100 instead of 6 to change the number of hours. The link I posted accounts for that.

Paul Newcome

@L@123 No worries.Took me a few meetings between my forehead and a wall before I figured out that's why it wasn't working for me. Haha! The thread I linked to converts the minutes to a decimal, tacks it on to the hours, then runs the calculations that way.

Paul Newcome

No worries. I think we will tweak this one to say that if the number of characters in the time cell is 3, then only pull the first digit, otherwise pull two (for four digit times).

This tweak would be something like

=IF(LEN([Time Column]@row) = 3, 1, 2)

We drop that in where we designate how many characters to pull in the LEFT function and update the column names appropriately, and we should be in business.

=((VALUE(LEFT([Sunday Close Military Time]1, IF(LEN([Sunday Close Military Time]@row) = 3, 1, 2))) + VALUE(RIGHT([Sunday Close Military Time]1, 2)) / 60) - (VALUE(LEFT([Sun Open Military Time]1, IF(LEN([Sun Open Military Time]@row) = 3, 1, 2))) + VALUE(RIGHT([Sun Open Military Time]1, 2)) / 60))

Paul Newcome

@Cheryl Petrich Assuming it would always be "12:00 am" and not go past that "1:00 am" the next day kind of thing then we can just use an IF statement to say that if the close time is "12:00 am" then output 24, otherwise convert the time as usual.

[Sun Open Military Time]:

=VALUE(LEFT([Sun Open]@row, FIND(":", [Sun Open]@row) - 1)) + IF(AND(LEFT([Sun Open]@row, 2) <> "12", CONTAINS("p", [Sun Open]@row)), 12) + (VALUE(MID([Sun Open]@row, FIND(":", [Sun Open]@row) + 1, 2)) / 60)

[Sunday Close Military Time]:

=IF([Sun Close]@row = "12:00 am", 24, VALUE(LEFT([Sun Close]@row, FIND(":", [Sun Close]@row) - 1)) + IF(AND(LEFT([Sun Close]@row, 2) <> "12", CONTAINS("p", [Sun Close]@row)), 12) + (VALUE(MID([Sun Close]@row, FIND(":", [Sun Close]@row) + 1, 2)) / 60)

[Sun Hours Duration]:

=[Sunday Close Military Time]@row - [Sun Open Military Time]@row

L_123

=(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)

Give that formula a try.

austinurbanSeagate

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

Cheryl Petrich

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)

L_123

Whoops. I always forget about the 12's. Give this a try.

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

Cheryl Petrich

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

Cheryl Petrich

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?

Paul Newcome

@Cheryl Petrich Take a look at THIS...

L_123

This will get rid of the colon, and should convert it to a number format so you can simply subtract the two cells.

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

Paul Newcome

@L@123 Unfortunately simply subtracting the two cells won't work as it will subtract the minutes based on 100 instead of 6 to change the number of hours. The link I posted accounts for that.

L_123

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

Paul Newcome

@L@123 No worries.Took me a few meetings between my forehead and a wall before I figured out that's why it wasn't working for me. Haha! The thread I linked to converts the minutes to a decimal, tacks it on to the hours, then runs the calculations that way.

Cheryl Petrich

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:

Paul Newcome

@Cheryl Petrich

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

Cheryl Petrich

@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))

Paul Newcome

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