Calendar/Leave planner and calculator

Hi all

I've spent most of today stealing peoples excel sheets from here and there to make my own leave sheets for the boss as we keep messing up how much we have left.

I've attached it so you can have a look.

Essentially it has all the days of the year on a sheet. A column of names, a column of leave entitled and a column of leave remaining. The latter is where my problem lies.


=B4-(COUNTIF(D4:CR4:D15:CR15:D26:CR26:D37:CR37,"=l")+(COUNTIF(D4:CR4:D15:CR15:D26:CR26:D37:CR37,"=h")/2))

If I put h in it subtracts .5 a day and if I put in l it takes away a full day. This works lovely until I get to the next trilogy of months. It subtracts from every ones leave.

Related is if the next row down adds a day it subtracts from mine? And so on

I hope it's clearer when you see it.

Thanks in advance