Calculate Shift Hours

[patrickavh]

Hi All, first post thanks for reading.

I have a spreadsheet where i want to input shift start time hour and end hour, to be inpuuted as number format i.e 10pm = 22

I need to calculate how many hours have been worked between

6 to 18
18 to 21
21 to 6

So if i input a start at 17 and end at 5

6 to 18 = 1
18 to 21 = 3
21 to 6 = 8

Sounds simple enough but i cannot crack it , not for the want of trying.

Hope you can help

Cheers Patrick

[AndyLitch]

Not sure I can help you because my arithmetic is different to yours.
I make
6 to 18 = 12
21 to 6 = 9

[Pepe Le Mokko]

I think you didn't see this sentence in the Op's post

So if i input a start at 17 and end at 5

His arithmetic is thus the same as your's : 1 hr worked between 6 and 18, 3 between 18 and 21 and 8 between 21 and 6

[Spitzerpl]

Hi Patrickavh,
I think this formula is what you are looking for:

Please Login or Register  to view this content.

This assumes column a contains your start hour and column B contains your End hour. The formula is filled down column C.

[patrickavh]

Hi All, thanks for the rsponse, the formula provided is not giving me the output i was looking for.

I almost need 3 Formulas that counts the hours worked between the 3 shifts.

There are that many IF's envolved i was hopping there might be a more suited function

[Spitzerpl]

Oh I see what your asking now. I'll be interested to see if someone comes up with a solution. Unfortunately I don't have time to work on it right now. My initial reaction is to figure out the total amount of time between the "6 to 18" and then subtract any time between the start Hour and the 6 as well as any time between the "18" and the end time.