# Create Date - Modified Date= Time Spent

Options

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.

• ✭✭✭✭✭✭
Options

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, ".", "")

• ✭✭✭✭✭✭
Options

Make sure they are Date columns...

• Options

@Paul Newcome , @Andrée Starå or anyone. Can you assist me please. Thanks in advance.

• ✭✭✭✭✭✭
Options

There is a solution tucked away in this thread here:

• ✭✭✭✭✭✭
Options

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, ".", "")

• ✭✭✭✭✭✭
Options

@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).

• ✭✭✭✭✭✭
Options
• Options

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

• ✭✭✭✭✭✭
Options

Make sure they are Date columns...

• Options

@Leibel Shuchat Sorry to bother you again.

Is it possible to have an average in "Sheet Summary" every month?

• Options

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?

• ✭✭✭✭✭✭
Options

@Erick Tirrel It sounds like you may be using the TODAY function somewhere. That could cause it.

@Leibel Shuchat Thanks!

• Options

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

• ✭✭✭✭✭✭
edited 04/25/22
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!