Determining duration in Hours:Minutes

Kevin Sitech
Kevin Sitech ✭✭
edited 12/09/19 in Formulas and Functions

Hey guys,

I have read several posts with several workarounds for calculating a value between a Start Time and an End Time where the input and result need to be in HH:MM. I have an idea and a beginning:

1. I created two System Columns. [Start Time] as 'Created (Date)' and [End Time] as 'Modified (Date).'

2. I created a [Completed] column as 'Checkbox.'

3 I created an [Interval] column to hold the time between [Start Time] and [End Time] which uses the simple formula:  =IF(Completed1 = "True", [End Time]1 - [Start Time]1)

This gives me raw time in "Smart Sheet Units" which seems to be: 1 Second = .0000116 SSU

 

If this is a feasible start, then I need to know if the following is possible.

4. Can I freeze or turn off the formula after the calculation is triggered by the checkbox so that the raw time is unaffected by further modification?

5. Convert the Raw time to Hours:Minutes?

6. Subtract non-work hours, weekends and holidays?

 

I am pretty sure I have seen you guys do everything but freeze a calculation. What do you think???

 

Thanks!

Kevin

 

 

 

 

Interval.PNG

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!