=SUM - Military Time conditional calculation Formula

bday2329 ✭✭✭✭
edited 12/09/19 in Formulas and Functions

Need help with calculation of military time:

I have two columns that I am using to track Admission time and Discharged time to determine and average length of stay.

I am using military time for each column and storing the difference in a third column for each row

=SUM(([Discharge Time From ER]2 - [Admit Time]2) / 100)

Exp DC time 300 – Ad time 100 = 2 hrs

However I was notified that we will be keeping track of admission times and discharge times that span past midnight (2400).

So I will have the Scenario where my admission time is 2300 hrs and my discharge time is 0200 hrs

Which with the above calculation will give me a negative number and hose up my averages

So can someone assist in providing a formula where if my calculation is negative to add 24 to the negative number otherwise if positive leave the value alone?

I am not that experienced with Smartsheet formulas and conditionals and could use the help.

I have provided a snapshot below.

Thank you in advance for your help!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!