Calculating hours worked - Excel Tips and Tricks

Here is a quick formula on how to calculate hours worked.

Use this formula.
=MOD(RIGHT(A2,5)-LEFT(A2,5),1)*24

Here is the break down of the formula.
Essentially, if your "Worked Times" cell value is this "8:00 - 17:00".
A) RIGHT(A2,5) return "17:00"
B) LEFT(A2,5) returns "08:00"
C) RIGHT(A2,5) - LEFT(A2,5) return "0.375"
D) MOD(RIGHT(A2,5) - LEFT(A2,5),1) return "0.375"
E) MOD(RIGHT(A2,5)-LEFT(A2,5),1)*24 return 9

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

[enthusia]

Or if you properly have start and end times in Columns A and B, just subtract them and format as Time in Column C.

[Michael_Alaska]

When using military time there is no colon. Does not work if you remove the colon and reduce characters to 4 spaces. (unless you minus right from left and divide by 100.).

[mslynnerose]

Hi, this was very helpful! Thank you. I have a question. What would the formula be if I want to add 3 shifts for 1 day? Please advise

[mjs]

i would like to 8 hours with 30 minutes break (start 7.30 end 16.00) include 30 minutes break. How way i calculate minus 30 minutes breaks ?

[gorflunk]

The MOD is unnecessary. It only returns the decimal portion of the mathematical operation (RIGHT minus LEFT) which is all you're going to get anyway unless the person works more than 24 hours. One day equals 1 in Excel, every hour, minute, or second is a fraction of that 1.

[lifeofjflo]

When I run this formula it just give me .5... what am I doing wrong?

[mahniac]

So if I want to calculate the sum of all hours worked in that month if it’s written in that format

[debashisdasjnvp]

What this parameter 5 mean, plz reply?

[ks_ethaydius]

Ok orrrr. Now hear me out. You can do the simple math in your head count 8 to 17. It's 8. Also normal work hours for anyone is 8 hours