Adding Time Values

New to smart sheet but couldn't find an answer to this question! Is there a way to add several time values to a total (ex. :15 + :15 = :30)?

My goal is to sum all overtime minutes worked during the week into a running yearly total. On my spreadsheet I have a Total YTD Hours Column, A column for each day of the week that would include the number of minutes worked, a Week Total column (to sum the days of the week), and an Updated YTD column (to sum the current YTD to overtime hours worked that week).

Thanks in advance!!

Find more posts tagged with

Sheets

Services

IT & Operations

Comments

Eid E. Eid

Hello,

I am new too to Smartsheet, but I have looked a bit into this, and did not find a direct way to do this.

There is a workaround invoving using a pseudo-decimal notation for the time, that I will try to describe as follows :

Use notation for 02:15 as 2.15 (like a decimal number). I will call X this notation.

The calculations with time cannot be done directly with 2.15 but in its equivalent in real minutes, which I will call M.

To obtain M, use =ROUND(X) * 60 + 100 * (X - ROUND(X))

After Calculation, to obtain back a notation like X,

use

=INT(M / 60) + (M - 60 * INT(M / 60)) / 100

I hope this helps...

Eid

Lindsey_123

Hi Ed!

Thanks for looking into this. Unfortunately I don't think this will work because I first need to Sum a few time values first. Ex. :15 (*7 days)= 105, which in your formula would think 1.05, which doesn't really align with what I would need it to use (1.45). If you have any other thoughts or workarounds please let me know!

Thanks,

Lindsey

Eid E. Eid

Hello Lindsey,

You would need to convert each of the individual times noted as "h.mm" to real minutes, using the formula above.

Perform the sums with the real minutes, then convert back the result to the h.mm notation using the formulas above.

I hope this helps,

Eid

Galen Garrison, PMP, CSM, MBA

I could use a time value as well. I need to create an implementation and cut-over plan that is down to time values in minutes, not just days, as the cut-over will occur over the course of several hours.