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

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Marilen.Navarro103391


    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 S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Marilen.Navarro103391

    Make sure they are Date columns...

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!