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:

Formulas for Calculating Time

Paul Newcome

Jun 8, 2020

I have decided to create a post that consolidates all of the different time based solutions that I have put together.

Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.

I thought it would be helpful to have them all in one place instead of spread out.

I welcome all feedback, suggestions, and additions.

I'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.

I will also try to post various tips and tricks as I think of them as well such as:

It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))

Calculating Time Worked for Employees

Can you calculate time in Smartsheet?

Conversion of timezones (Solved Formula Included)

Date/Time/Year

Flagging a Date and Time Overlap

How do I create time of day columns?

Need to create a "shift" column from a time column

---

View Post

Paul Newcome

You should be able to find what you need here:

Formulas for Calculating Time

Paul Newcome

Jun 8, 2020

I have decided to create a post that consolidates all of the different time based solutions that I have put together.

Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.

I thought it would be helpful to have them all in one place instead of spread out.

I welcome all feedback, suggestions, and additions.

I'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.

I will also try to post various tips and tricks as I think of them as well such as:

It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))

Calculating Time Worked for Employees

Can you calculate time in Smartsheet?

Conversion of timezones (Solved Formula Included)

Date/Time/Year

Flagging a Date and Time Overlap

How do I create time of day columns?

Need to create a "shift" column from a time column

---

View Post

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. 👍️