Working hours calculation

[john55]

Hi,
I have a file for roster and some simple calculations for working hours using simple formulas.
Sometimes a man has a different schedule than his regular schedule, what I want is to calculate working hours.
I attached a test file for your reference, in yellow is the correct calculation.
Hope this can be done!
Thank you very much for your help!

[Aardigspook]

In AH12 and AI12, try these:

Please Login or Register  to view this content.

Your formulae used Z* and N* which will also find just Z and N, so you need to exclude those.
You could replace the "<>Z" with "<>"&LEFT(AH$11,1) if you want the formulae to be able to be expanded to deal with other similar situations, but I thought that was a bit over the top for now .

Hope that helps.

[CAntosh]

The "*" in AH11:AI11 is throwing things off a bit. Try the following in AH12:

=(COUNTIF($C12:$AG12,LEFT(AH$11,1)&"*")-COUNTIF($C12:$AG12,LEFT(AH$11,1)))*AH$10

fill down, fill right. See attached:

[CAntosh]

I thought that was a bit over the top for now .

Ha! I had the same thought when crafting my initial effort, then I figured, "Why not?" and made the change.

[john55]

Thanks a lot to both of you!
that's it!

[CAntosh]

Glad to help, good luck!

[Aardigspook]

You're welcome, glad to be of help and thanks for the rep.