Calculating duration with start/end day and time columns?
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!
Best Answer
-
You should be able to find what you need here:
Answers
-
You should be able to find what you need here:
-
This is a great resource, thank you very much Paul! The Calculating Time Worked for Employees sheet did the trick. Cheers!
-
Happy to help. 👍️
-
@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)
-
@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), ………………………………..
-
You are the best!!!!!!!! 💪💰💰🤸♀️🤾♂️🏄♀️🕺💃. It worked- Thank you - Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!