Formulas for Calculating Time

mushtaq90006

I am trying to calculate number of minutes between start and end time. It is working fine untill there is no Date change. On Date change it gives me negative minutes since i am using 24 hour clock.

=(VALUE(LEFT([Actual End (UTC+0300)]@row, 2)) + (VALUE(RIGHT([Actual End (UTC+0300)]@row, 2)) / 60 - (VALUE(LEFT([Actual Start (UTC+0300)]@row, 2)) + (VALUE(RIGHT([Actual Start (UTC+0300)]@row, 2)) / 60)))) * 60

Windrich Group

I am getting an invalid formula when trying to calculate the total time between a start time and stop time. Can anyone help with the correct formula?

Tim C

You can use the new time function to achieve the time difference you are looking for, see the community post here.

Also a community member put together this awesome video on the time function, worth the watch.

https://youtu.be/J2jrhCfsDy0

Paul Newcome

@Windrich Group Your syntax is off.

Try something like this:

=TIME(TIME([Stop Time]@row) - TIME([Start Time]@row)) * 24

The above will output the duration in hours.

Tim C

@Paul Newcome with the save again! Thanks Paul. You should do a Smartsheet webinar on the time function 😄

Romano el Polako

Hey Smart Heads!

Excellent job @Paul Newcome on the date formula master article!

I have tried to do similar exercise to Calculating Time Worked for Employees :

Scenario:

• IR Time and Hand Over Time is provided in strange format, hence I have used following:

=LEFT([IR Time]@row, 5)

=LEFT([Hand Over Time]@row, 5)

Formula in Lead-Time Calculation:

=((VALUE(LEFT([Hand Over Time - Hours]@row, FIND(":", [Hand Over Time - Hours]@row - 1)) + VALUE(RIGHT([Hand Over Time - Hours]@row, 2)) / 60) + ([Hand Over Date]@row - [IR Date]@row) * 24) - (VALUE(LEFT([IR Time - Hours]@row, FIND(":", [IR Time - Hours]@row) - 1)) + VALUE(RIGHT([IR Time - Hours]@row, 2)) / 60))

is giving me #INVALID OPERATION error : (

• I suspect that the issue might be triggered by using =LEFT formula to extract 5 digit
• well... I have just came up with an idea to slightly modify the formula to extract the time from IR - Time and not from IR Time - Hours (with LEFT), will keep you updated.

@Upatde

I have additionally tried following:

=((VALUE(LEFT([Hand Over Time]@row, FIND(":", [Hand Over Time]@row - 1)) + VALUE(MID([Hand Over Time]@row, 4, 2)) / 60) + ([Hand Over Date]@row - [IR Date]@row) * 24) - (VALUE(LEFT([IR Time]@row, FIND(":", [IR Time]@row) - 1)) + VALUE(MID([IR Time]@row, 4, 2)) / 60))

with the same results:

If you can help, please let me know, thanks!

Romano

Paul Newcome

@Romano el Polako I haven't looked through in too much detail yet, but one thing I do notice is that you have multiple parenthesis either missing or misplaced. Which formula from the thread are you trying to replicate?

Romano el Polako

Hey @Paul Newcome

Thank you for quick response, I was trying to use your formula:

=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)

Thanks,

Romano!

Paul Newcome

@Romano el Polako In that case, make sure you only change column names. Adjusting the parenthesis will break the formula.

Romano el Polako

Hey @Paul Newcome

I don't know how, why and by what special force, but the formula actually works after aligning nomenclature with the column names from your query, outstanding, thanks again!

Romano

Romano el Polako

Hey @Paul Newcome and Smart Heads!

Sorry for another request, but I would like to step-up the game when it comes to time calculation.

1. As presented above, I have access to A. Start Date + Time and B. End Date + Time.

1. Thanks to Palu's Formula, I have:

• Difference in Hours + Minutes
• SUM: in 1/100 values
• Sum_Days: =SUM@row / 24

1. As a next step, I have added a column for NETWORKDAYS =NETWORKDAYS([Start Date]@row, [End Date]@row)
2. And finally added a column to calculate SLA Time (SLAs (service level agreements), time from intake to task completed):
3. =IF([SUM_Days]@row > NETWORKDAYS@row, [SUM_Days]@row - ([SUM_Days]@row - NETWORKDAYS@row), [SUM_Days]@row)
4. In other words, IF SUM Days is longer that count of networkdays, then we consider networkdays as SLA. If SUM Days is shorter than Networkdays, then we consider SUM Days.

Desired next Steps:

• Define SLA in more precise way. Substract from SUM_Days (in hours) Time of Weekends and Holidays (in hours as well).
• I think that this form can be pretty complex, but having this formula can be helpful for many Smart-Users.
• Thinking long term, we could raise a request to have =NETWORKHOURS formula in SmartSheet to avoid any walk-arounds.
• 
  
• Looking forward to hearing from you, Thanks! Romano

Romano el Polako

@Update:

• Short Term Goal: I think I have managed to end-up with pretty solid count of net days, by adjusting Project Settings, see:

kawind

Can someone please post the formula for the calculation between column start time and column end time - with the format 7:30pm

Paul Newcome

@kawind That solution is already posted in a number of places here in this thread.

Romano el Polako

Hello Darkness, my old friend!

As a follow-up to my previous comment, I have found a gap in my duck-tape solution.

My formula defined a case as out of SLA for the scenario of the weekend, see:

Full view:

Networkdays formula correctly provided result of 2, considering 1/19 - Friday, and 1/22 - Monday.

Correct result would be here:

24-16.33 = ~7.7 from Friday

~12.8 Monday

Total: 20.5 hours = <24 = IN SLA

-------------

NETWORKDAYS Formula:

=IFERROR(NETWORKDAYS([Start Date]@row, [End Date]@row), "")

SLA_Time Formula:

=IFERROR(IF([SUM_Days]@row > NETWORKDAYS@row, [SUM_Days]@row - ([SUM_Days]@row - NETWORKDAYS@row), [SUM_Days]@row), "")

--------------

Do you have any idea how to fix it up?

Thanks,

Romano