Formula to calculate working hours

Waleed

Hello Team ,

Please see attached photo above

Im looking for away to count my employees daily working hours (in working hrs col)

The Col names ( Shift Start and Shift End) are select down menu for 24/hrs

(hrs and minutes separated with colons)

I Need a formula for column (Working/hrs) that count the number of employee's working hours and to consider the 24 hours format in case the shift in two different days .

For example :

Employee start Shift at 18:00 (evening) and end shift at 03:00 (morning Time) . we need the formula to count the number of working hours and minutes which is (8 hrs and 50 min )

And to count the same hours if oppsite (started evening and end shift next day morning time)

Thank you , for your assistance

Hopefully one day SmartSheet to develop a column type feature for date/time and time with automation functions, im waiting for years with hope to see this feature on smartsheet

Waleed

I would appreciate if to let me know if there is no formula for this request not to worry about it or work around

😔

Andrée Starå

Hi @Waleed

I hope you're well and safe!

Look at Paul's extensive post with different kinds of time solutions; at least one of them will be what you need.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Waleed

Hi @Andrée Starå

I would like to invite @Paul Newcome

i tried the samples foumulas users were posting, it did not work for me

im still need assitance on this with a formula for exact giving sample, im newbie into this 😔

Genevieve P.

Hi @Waleed

Paul's first link there should be a similar solution to what you need, this one: Calculating Time Worked for Employees

First Column formula updated to have your column names:

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

Waleed

Hi @Genevieve P.

Thank you for assistance.

I tried same code on column Total Time to calculate the number of working hours/min

Unfortunately, it did not work for me , error message : #Invalid operation

Genevieve P.

Hi @Waleed

My apologies, in translating over to your column names I replaced two of the cells with the wrong name.

Two of the references are to Date columns in your sheet the Start Date and End Date- take a look at Paul's published sheet for an example.

I've bolded the two references you'll need to change to be associated with a Date column:

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

Cheers,

Genevieve

Waleed

Hi @Genevieve P.

Amazing, it works perfect , i got the results i was looking for

Your assistance made our day , much appreciated , so many thanks Ms. Genevieve

The only issue i have that the column where i placed the formula shows error message #INVALID VALUE in case some targeted cells are blank , i must fill them all and error is replaced with result.

Is there a way not to have this error message. This is my final formula im using

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

Thank you again

Genevieve P.

Hi @Waleed

I'm glad I could help!

To get rid of the error, you can add an IF statement at the front that says IF the cell is blank, return blank, otherwise do your formula:

=IF([End Shift]@row = "", "", formula)

or

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

Cheers,

Genevieve

Waleed

Hi @Genevieve P.

It worked perfect ,

I feel more powerful with smart sheet having it customized for most of our workflow , many thanks for assistance