Calculating Military Time from am/pm format

Sharon Castiglia

Hello,

Trying to convert am/pm format to a military time format (i.e. 12:30 am would read 0030, 11:00 pm would read 2300, 8:15 pm would read 2015) from there I would be able to calculate a duration time by subtracting the start time from the end time.

I have scoured the existing solutions out on the community and none are working (hrs in one column, minutes in another, etc)

Any help would be greatly appreciated.

Sharon C.

Paul Newcome

Ok. So to convert your Start Time into a number:

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

To convert your end time (with dates taken into account):

=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + IF(CONTAINS("p", [End Time]@row), IF(VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) = 12, -12))) + (VALUE(MID([End Time]@row, FIND(":", [End Time]@row) + 1, 2)) / 60)) + (([End Date]@row - [Start Date]@row) * 24)

This will convert each into a numerical value where you can subtract the start from the end to get your duration. The duration would end up being a numerical value as well. For example, if the duration were four hours and thirty minutes...

4:30 = 4 1/2 hours = 4.5

with 4.5 being the output of the formulas above. Let me know if you wanted to convert that 4.5 back into a format such as hh:mm.

Paul Newcome

Ok. To convert the duration back into hh:mm format, we would use something like this...

=INT([Install Duration]@row) + ":" + ([Install Duration]@row - INT([Install Duration]@row)) * 60

This would go into a separate column.

I do suggest using two separate columns too. One for the number and another for the display. The display is a text string and cannot be used in numerical calculations, so keeping the number column (even if it is hidden) allows you to pull metrics such as average install time per person or total install time for John Doe during month x and then you can just display the hh:mm column.

Paul Newcome

I have quite a bit of different notes on time. I'll go ahead and start digging. In the mean time... Will you also be including dates in your calculations such as starting at 11:00PM on 1 April and ending at 3:00AM on 2 April?

I ask because if you are doing straight time calculation and not involving dates, it is advisable to actually use one formula for the start time and a different for the end time. The reason for this would be that midnight is normally converted to 0000, but if your end time is midnight, then your duration is going to be off.

I usually suggest converting start time midnight to 0000 and end time midnight to 2400 if dates are not involved.

If dates are involved, I leverage the dates themselves to cover that and it ends up being the same formula for start and finish.

Paul Newcome

Additionally... How is your time currently formatted? Is AM/PM in the same cell as the time or is it in a different column? That will make a difference as well.

Sharon Castiglia

Start times can be either before or after midnight. Start times are formatted with AM/PM in the same cell.

Paul Newcome

I understand that the start time could be before or after midnight, but what about the end time in relation to the start time? Is it possible the end time will have a different date such as starting at 11:00PM on 1 April and ending at 3:00AM on 2 April?

Sharon Castiglia

Apologies for missing that. Example, starting 11:00 PM on 3/31 and ending 6:00 AM on 4/1.

Paul Newcome

No worries. And finally... Are the Dates in a date type column or are you using more along the lines of system generated Created (date) and Modified (date) type columns?

Sharon Castiglia

They are in a date type column.

Paul Newcome

Ok. So to convert your Start Time into a number:

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

To convert your end time (with dates taken into account):

=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + IF(CONTAINS("p", [End Time]@row), IF(VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) = 12, -12))) + (VALUE(MID([End Time]@row, FIND(":", [End Time]@row) + 1, 2)) / 60)) + (([End Date]@row - [Start Date]@row) * 24)

This will convert each into a numerical value where you can subtract the start from the end to get your duration. The duration would end up being a numerical value as well. For example, if the duration were four hours and thirty minutes...

4:30 = 4 1/2 hours = 4.5

with 4.5 being the output of the formulas above. Let me know if you wanted to convert that 4.5 back into a format such as hh:mm.

Sharon Castiglia

Paul,

That was awesome. Converting to the hh:mm would be awesome. Install duration is Store Open Time - Tech Arrival Time. The -15 concerns me a little.

Thank you so very much with helping me out with this.

Sharon Castiglia

Sorry on the -15. Forgot to change the start date to 3/18.

Paul Newcome

Ok. To convert the duration back into hh:mm format, we would use something like this...

=INT([Install Duration]@row) + ":" + ([Install Duration]@row - INT([Install Duration]@row)) * 60

This would go into a separate column.

I do suggest using two separate columns too. One for the number and another for the display. The display is a text string and cannot be used in numerical calculations, so keeping the number column (even if it is hidden) allows you to pull metrics such as average install time per person or total install time for John Doe during month x and then you can just display the hh:mm column.

Sharon Castiglia

Paul,

Well you are my hero🏆️🥇🏆️. Everything worked great.

Paul Newcome

Excellent! Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.

Anthony Barthelemy

Either of you have a Template you can Share so I do not have to start from Scratch?

Thanks

Gwendolene Day

https://community.smartsheet.com/discussion/comment/244966#Comment_244966

Hi @Paul Newcome - Resurrecting this old thread and hoping you might have insight to help me with the formulas you've suggested. They seem like they will work perfectly for my needs, converting Standard to Military then numeric, so I can add a calculated duration (duration/60) and convert it back to Military, then Standard time.

However, when my calculated End Num value is an whole number, (19.00), the formula =INT([End Num]@row) + ":" + ([End Num]@row - INT([End Num]@row)) * 60 is giving me 19:0 instead of 19:00. Increasing decimal on the column does not do anything. I'm pulling the End to Start Time of the next row (a simple =End36) because I get circular reference when trying to leverage =INDEX(End:End), Row@row-1,1) for the next row's start time). That missing 0 is throwing the rest of the sheet into Invalid Value. Appreciate any ideas you have to fix this formula. Thanks!