Annual Leave Formula Help

[CMcCormack1]

Hi All,

I have a excel spreadsheet that acts as the ROTA for our team and there are 2 sections of which i need some assistance.

1. We have a 'Summary Page' which is similar to the below table. This summary page, links to each sheet (one for each month) and automatically calculates how many time the letter 'H' has been entered and then subtracts it from the amount available at the start of the year. For E.g begin year with 24 days and take 3 days in january and 1 in February.

Employee Name	January	February	Total Remaining
Employee #1	3	1	20
Employee #2	2	4	18

What is needed is for this to also calculate the 'half day holidays' which is tracked using 1/2H on the monthly sheets. This is the current formula that is in place =COUNTIF(Jan!$B2:Z$2,"H").

Does anyone know how to edit that formula so it adds the 1/2H and logs them as 0.5 days on the summary page.


2. The 2nd is similar, however actually on the sheets itself, we have a key after all the members of the team in which it gives us a mini summary of each day of who is on Holiday/Training/Sick etc and its the same as above regarding adding the functionality of half day holidays in the form of 1/2H. This is the current formula in place. =COUNTIF(F2:F73,"H"). Same again where 'H' = 1 full day. 1/2H = 0.5 day.

Any assistance is welcome and appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIF(Jan!$B2:Z$2,"H")+COUNTIF(Jan!$B2:Z$2,"1/2H")*0.5

 

[CMcCormack1]

+COUNTIF(Jan!$B2:Z$2,"1/2H")*0.5

Hi Fluff,

I have tried that on the spreadsheet and has worked and is now calculating the .5 days.

Many thanks for the quick reply, much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.