Calculating duration with start/end day and time columns?

mcdonm

Hi,

I'm looking for a way to calculate duration (preferably in hours) with start and end day/time columns (as pictured below). Currently entering duration manually and it's not ideal. The sheet in particular is for system downtime metric tracking and so knowing the date and time are obviously necessary. Date columns are properly formatted as dates and time columns are currently formatted as text/number. Any ideas?

Thanks!

Paul Newcome

You should be able to find what you need here:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Paul Newcome

You should be able to find what you need here:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

mcdonm

This is a great resource, thank you very much Paul! The Calculating Time Worked for Employees sheet did the trick. Cheers!

Paul Newcome

Happy to help. 👍️

ssuser01

@Paul Newcome Can you assist me with Calendar date + Duration = Targeted Completion date.

My Calendar Date field has a formula that creates a date and the column type is a date. My duration field has a formula in it that creates a duration based off information from another field. My Targeted Completion date has a formula which is suppose to take the calendar date and add the duration to create the targeted completion date. It seems that the formula is not working I would appreciate your assistance. I looked at some of the short cuts you have provided, but I seem to still have an issue. Below I will show you my fields:

Calendar Date Formula:

I have tried with quotes around the date and with out the date. - Neither work.

=IF(Timeframe@row = "November", 11 / 1 / 2024, IF(Timeframe@row = "January", 1 / 1 / 24, IF(Timeframe@row = "February", 2 / 1 / 24, IF(Timeframe@row = "March", 3 / 1 / 24, IF(Timeframe@row = "April", 4 / 1 / 24, IF(Timeframe@row = "May", 5 / 1 / 24, IF(Timeframe@row = "June", 6 / 1 / 24, IF(Timeframe@row = "July", 7 / 1 / 24, IF(Timeframe@row = "August", 8 / 1 / 24, IF(Timeframe@row = "September", 9 / 1 / 24, IF(Timeframe@row = "October", 10 / 1 / 24, IF(Timeframe@row = "December", 12 / 1 / 24))))))))))))

Duration of Time to Complete Formula:

=IF([Timeframe to Complete]@row = "28 Calendar Days Prior to the Commitment Read Out", 10, IF([Timeframe to Complete]@row = "Complete the last week before end of quarter", 28, IF([Timeframe to Complete]@row = "Within one week after annual planning completes", 7, IF([Timeframe to Complete]@row = "Complete the first week of the Month", 10, IF([Timeframe to Complete]@row = "Minimum 1 Week Prior to kick off", 22, IF([Timeframe to Complete]@row = "10 calendar days Prior to Kick Off", 20, IF([Timeframe to Complete]@row = "Complete by the end of the month", 28)))))))

Test Target Date

=([Calendar Date]@row + [Duration of Time to Complete review2]@row)

Paul Newcome

@ssuser01 The issue is with the initial formula that outputs in the [Calendar Date] column. To get data output as an actual date as opposed to a text string that looks like a date, you need to leverage the DATE function.

DATE(yyyy, mm, dd)

=IF(Timeframe@row = "November", DATE(2024, 11, 01), ………………………………..

ssuser01

You are the best!!!!!!!! 💪💰💰🤸‍♀️🤾‍♂️🏄‍♀️🕺💃. It worked- Thank you - Thank you!

Paul Newcome

Happy to help. 👍️