Change Time into Military

Cheryl Petrich · 01/22/20

Sorry - here you go! Meant to add that too!

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

Paul Newcome · 01/22/20

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

Cheryl Petrich · 01/22/20

Perfection!! Thank you so much @Paul Newcome - that worked! Appreciate everyone's help here. Saved me hours of muddling around.

Paul Newcome · 01/22/20

Happy to help! 👍️

Cheryl Petrich · 09/10/20

Another question on the same string... @Paul Newcome maybe you can help? We are now allowing 12:00am in the Sunday close and it messes with my formula.

Here's the formula and an example: =VALUE((VALUE(LEFT([Sun Close]1, FIND(":", [Sun Close]1) - 1)) - IF(VALUE(LEFT([Sun Close]1, FIND(":", [Sun Close]1) - 1)) = 12, 12, 0) + IF(RIGHT([Sun Close]1, 2) = "pm", 12, 0)) + MID([Sun Close]1, FIND(":", [Sun Close]1) + 1, 2))

At first, I thought maybe I'd be tricky and change the "pm" to "am" and put 24 within the formula - works with 12:00am, but that causes everything else to be wrong (see first row Sunday Close). If I use the formula above, it returns 0 for Midnight. I can always change the selection to "Midnight" if that would be easier to just add that into the statement vs. numbers.

Any help is appreciated.

Paul Newcome · 09/10/20

@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

Cheryl Petrich · 09/10/20

@Paul Newcome THANK YOU!! I ended up only changing my current statement on the Close column from:

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

to

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

And that seems to have worked without touching the open column (we aren't changing any times there).

Paul Newcome · 09/10/20

Happy to help. 👍️

Are you only using whole hours? I ask because your current setup may not provide accurate results in certain circumstances if you had times such as 1:23 am or 4:30 pm where the minutes are not "00".

Cheryl Petrich · 09/10/20

We are using set values but only whole and half (like 4pm and 430pm). It seems to still work and I've tried all sorts of variables.

Data sets are open: 7:00 am, 7:30 am, 8:00 am, 8:30 am, 9:00 am, 9:30 am, 10:00 am, 10:30 am, 11:00 am, 11:30 am, 12:00 pm, 12:30 pm, 1:00 pm

And closed: 4:00 pm, 4:30 pm, 5:00 pm, 5:30 pm, 6:00 pm, 6:30 pm, 7:00 pm, 7:30 pm, 8:00 pm, 8:30 pm, 9:00 pm, 9:30 pm, 10:00 pm, 10:30 pm, 11:00 pm, 11:30 pm, 12:00 am

Cheryl Petrich · 09/10/20

And remember it needs to show military time.

Paul Newcome · 09/10/20

I just went back through the thread and saw your post with the actual calculation. I was looking at your open and close conversion formulas and didn't see anything where you were dividing or multiplying by 60 to convert minutes to hours for the decimal portion, but I see now that you are doing that in your final calculation. Sorry about that.

Cheryl Petrich · 09/10/20

NOOO problem at all - you have been sooo amazing and I appreciate it!! I couldn't do it without YOU!! And, everyone else in this community!

Paul Newcome · 09/10/20

Happy to help. 👍️

Adrian Garcia · 03/23/21

https://community.smartsheet.com/discussion/comment/237699#Comment_237699

I'm trying to find the duration and the formula I'm using is correct but for times like "900" I'm getting a negative number. How can I implement this same thing but in the formula I'm using?

=(VALUE(LEFT([End Military]@row, 2)) + VALUE(RIGHT([End Military]@row, 2)) / 60) - (VALUE(LEFT([Start Military]@row, 2)) + VALUE(RIGHT([Start Military]@row, 2)) / 60)

Paul Newcome · 03/24/21

@Adrian Garcia You would implement it the same way. The IF/LEN would go in the second portion of the LEFT functions to tell them to pull either one or two digits based on the overall character count.

So instead of

LEFT(Time@row, 1)

you would have

LEFT(Time@row, IF(LEN(Time@row) = 3, 1, 2))

Change Time into Military

Answers

Categories