Modify teh present formula

Hello,

I'd like to have a formula in order to subtract hours take into account 1 condition.
-If he/she is on duty (the simbol can be: N, N1, N2, N3, N4) in the last day of month then subtract 6.
I use for calculation this one:
Col AQ: COUNTIF($C12:$AG12,AQ$11)*AQ$10
for example, the first guy works 4 nights, I multiply by 8, result 32, now because he works in the last day of month I need to subtract 6 hours, so the result should be 32-6=26.

Attached a test file for your reference.
Thank you very much for your help!

Try

Aq12

Formula:

Please Login or Register  to view this content.

That's it! Thank you very much for your help!

Another way.

=COUNTIF($C12:$AG12,AQ$11)*AQ$10-ISNUMBER(FIND({"N","N1","N2","N3","N4"},AD12))*6

Shukla, thank you, but the last day of month can be col AD, AE, AF or AG....

In that case change my formula to

=COUNTIF($C12:$AG12,AQ$11)*AQ$10-ISNUMBER(FIND({"N","N1","N2","N3","N4"},INDEX($C12:$AG12,MATCH("zzzz",$C$11:$AG$11,1))))*6

Originally Posted by john55

Shukla, thank you, but the last day of month can be col AD, AE, AF or AG....

Then Try

Formula:

Please Login or Register  to view this content.

Shukla, "Ref" ... with the last formula, I tried ctrl+shift+enter but I have Ref in cell..

Originally Posted by john55

Shukla, "Ref" ... with the last formula, I tried ctrl+shift+enter but I have Ref in cell..

There is no need to CSE.

Please check the attached file here it is working great.

Flame, it works, I test it for dif dates but it seems it's working!

Thanks to all of you!
That's it!

You're welcome. Thanks for the feedback and rep.