# Calculating Military Time from am/pm format

Options
✭✭✭✭✭

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.

«1

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭
Options

They are in a date type column.

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

Paul,

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

Thanks

• Options

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!