Create Date - Modified Date= Time Spent
Hello,
Can you help me with the formula for "Time Spent" Column. I need to calculate how long it would take to process the order. Sometimes we process for days. I need to know how many days, hours or minutes.
Thank you so much.
Best Answers
-
Below is an example of what columns you can add to achieve what you are looking for. It assumes you have already the 2 'system' columns titled, Created, and Modified.
Title: Start Date
Formula:
=DATEONLY(Created@row)
Title: Start Time
Formula:
=IF(SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1))) = 12, 0, SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))) + IF(RIGHT(Created@row, 2) = "PM", 720, 0) + 1
Title: End Date
Formula:
=DATEONLY(Modified@row)
Title: End Time
Formula:
=IF(SUM(VALUE(LEFT(RIGHT(Modified@row, LEN(Modified@row) - 9), FIND(":", RIGHT(Modified@row, LEN(Modified@row) - 9)) - 1))) = 12, 0, SUM(VALUE(LEFT(RIGHT(Modified@row, LEN(Modified@row) - 9), FIND(":", RIGHT(Modified@row, LEN(Modified@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID(Modified@row, FIND(":", Modified@row) + 1, 2))) + IF(RIGHT(Modified@row, 2) = "PM", 720, 0) + 1
Title: Duration Minutes
Formula:
=SUM((SUM([End Date]@row - [Start Date]@row) * 24) * 60) + IF([Start Date]@row < [End Date]@row, SUM(1440 - [Start Time]@row) + [End Time]@row, [End Time]@row - [Start Time]@row)
This final column is the one that will give you the formatted days, hours, minutes:
Title: Duration
Formula:
=IF([Duration Minutes]@row > 1439, ROUNDDOWN(SUM([Duration Minutes]@row / 60) / 24, 0) + " Day" + IF(ROUNDDOWN(SUM([Duration Minutes]@row / 60) / 24, 0) > 1, "s", "") + ", ", "") + IF(MOD([Duration Minutes]@row, 1440) > 59, ROUNDDOWN(SUM(MOD([Duration Minutes]@row, 1440) / 60), 0) + " Hour" + IF(SUM(MOD([Duration Minutes]@row, 1440) / 60) > 1, "s", "") + ", ", "") + IF(MOD([Duration Minutes]@row, 60) > 0, MOD([Duration Minutes]@row, 60) + " Minute" + IF(MOD([Duration Minutes]@row, 60) > 1, "s", ""), "") + IF([Duration Minutes]@row > 0, ".", "")
-
Make sure they are Date columns...
Answers
-
@Paul Newcome , @Andrée Starå or anyone. Can you assist me please. Thanks in advance.
-
There is a solution tucked away in this thread here:
-
Below is an example of what columns you can add to achieve what you are looking for. It assumes you have already the 2 'system' columns titled, Created, and Modified.
Title: Start Date
Formula:
=DATEONLY(Created@row)
Title: Start Time
Formula:
=IF(SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1))) = 12, 0, SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))) + IF(RIGHT(Created@row, 2) = "PM", 720, 0) + 1
Title: End Date
Formula:
=DATEONLY(Modified@row)
Title: End Time
Formula:
=IF(SUM(VALUE(LEFT(RIGHT(Modified@row, LEN(Modified@row) - 9), FIND(":", RIGHT(Modified@row, LEN(Modified@row) - 9)) - 1))) = 12, 0, SUM(VALUE(LEFT(RIGHT(Modified@row, LEN(Modified@row) - 9), FIND(":", RIGHT(Modified@row, LEN(Modified@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID(Modified@row, FIND(":", Modified@row) + 1, 2))) + IF(RIGHT(Modified@row, 2) = "PM", 720, 0) + 1
Title: Duration Minutes
Formula:
=SUM((SUM([End Date]@row - [Start Date]@row) * 24) * 60) + IF([Start Date]@row < [End Date]@row, SUM(1440 - [Start Time]@row) + [End Time]@row, [End Time]@row - [Start Time]@row)
This final column is the one that will give you the formatted days, hours, minutes:
Title: Duration
Formula:
=IF([Duration Minutes]@row > 1439, ROUNDDOWN(SUM([Duration Minutes]@row / 60) / 24, 0) + " Day" + IF(ROUNDDOWN(SUM([Duration Minutes]@row / 60) / 24, 0) > 1, "s", "") + ", ", "") + IF(MOD([Duration Minutes]@row, 1440) > 59, ROUNDDOWN(SUM(MOD([Duration Minutes]@row, 1440) / 60), 0) + " Hour" + IF(SUM(MOD([Duration Minutes]@row, 1440) / 60) > 1, "s", "") + ", ", "") + IF(MOD([Duration Minutes]@row, 60) > 0, MOD([Duration Minutes]@row, 60) + " Minute" + IF(MOD([Duration Minutes]@row, 60) > 1, "s", ""), "") + IF([Duration Minutes]@row > 0, ".", "")
-
@Leibel Shuchat You mind if I copy this over to the time thread (and tag you for credit of course)? I think this is different from any of the other solutions posted there (I am positive it is different from any of the ones that I have come up with).
-
-
Hi. All formula are working on my end except Start Date and End Date.
I'm getting " #INVALID COLUMN VALUE" if I enter =DATEONLY(Created@row)
Almost there! thank you so much
-
Make sure they are Date columns...
-
@Leibel Shuchat Sorry to bother you again.
Is it possible to have an average in "Sheet Summary" every month?
-
Hi all I used this formula and it worked great!
My issue is that the modified date column is changing anytime I use the sheet, as opposed to only updating at the row level.
Any insight?
-
@Erick Tirrel It sounds like you may be using the TODAY function somewhere. That could cause it.
@Leibel Shuchat Thanks!
-
Hello Guys,
I am new to SS and would really appreciate your assistance (I am also trying to calculate the total processing time of requests). I would like to thank Leibel Shuchat for thís great solution. it is working perfectly fine except that some outputs are not calculated correctly... The duration for the highlighted dates should be 1 day, 3 hours, 53 minutes & not 2 days.... it seems that its adding an extra day.. @Paul Newcome, @Leibel Shuchat could you please advise how to fix that.. Ps: my time format is 24h...
Thank you so much for your help!
Let me know if you need any further info..
-
Change the Duration Minutes column to the below, seems like i made an error here.
=SUM((SUM([End Date]@row - [Start Date]@row - 1) * 24) * 60) + IF([Start Date]@row < [End Date]@row, SUM(1440 - [Start Time]@row) + [End Time]@row, [End Time]@row - [Start Time]@row)
If you want a different kind of formula, try the below option.
https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time/p9#
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
