SOLVED

KEEPING TRACK OF HOLIDAYS IN EXCEL

%3CLINGO-SUB%20id%3D%22lingo-sub-2348652%22%20slang%3D%22en-US%22%3EKEEPING%20TRACK%20OF%20HOLIDAYS%20IN%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2348652%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20please.%20I%20would%20like%20to%20keep%20track%20of%20holidays.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20calendar%20and%20in%20each%20week%20marked%20H%20on%20the%20day%20holiday%20was%20taken%2C%20I%20would%20ideally%20like%20to%20do%20the%20following.%3C%2FP%3E%3CP%3EColumn1%20to%20show%20initial%20entitlement%20of%2020%20days%20(excludes%20bank%20hols)%3C%2FP%3E%3CP%3EColumn2%20to%20add%20all%20the%20H's%26nbsp%3B%20-%20confirming%20how%20many%20holidays%20have%20been%20taken%3C%2FP%3E%3CP%3EColumn3%20to%20deduct%20Col2%20from%20Col1%20-%20to%20give%20total%20remaining%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20above%20would%20be%20useful%20when%20asked%20on%20the%20spot%20how%20many%20holidays%20an%20employee%20has%20left.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20very%20basic%20knowledge%20of%20excel%20so%20you%20will%20have%20to%20excuse%20me%20if%20I%20ask%20if%20you%20could%20explain%20any%20suggestions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%20for%20your%20helpful%20suggestions%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2348652%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2348736%22%20slang%3D%22en-US%22%3ERe%3A%20KEEPING%20TRACK%20OF%20HOLIDAYS%20IN%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2348736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725054%22%20target%3D%22_blank%22%3E%40AlisonAMK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20mark%20the%20H's%20(or%20not)%20in%20H2%3ANH2%3C%2FP%3E%0A%3CP%3EIn%20A2%3A%2020%3C%2FP%3E%0A%3CP%3EIn%20B2%3A%20%3DCOUNTIF(N2%3ANH2%2C%22H%22)%3C%2FP%3E%0A%3CP%3EIn%20C2%3A%20%3DA2-B2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20A2%3AC2%20and%20fill%20down%20as%20far%20as%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

Can someone help me please. I would like to keep track of holidays. 

 

I have created a calendar and in each week marked H on the day holiday was taken, I would ideally like to do the following.

Column1 to show initial entitlement of 20 days (excludes bank hols)

Column2 to add all the H's  - confirming how many holidays have been taken

Column3 to deduct Col2 from Col1 - to give total remaining

 

The above would be useful when asked on the spot how many holidays an employee has left.

 

I have very basic knowledge of excel so you will have to excuse me if I ask if you could explain any suggestions.

 

Many thanks in advance for your helpful suggestions

 

2 Replies
best response confirmed by AlisonAMK (Occasional Contributor)
Solution

@AlisonAMK 

Let's say you mark the H's (or not) in H2:NH2

In A2: 20

In B2: =COUNTIF(N2:NH2,"H")

In C2: =A2-B2

 

Select A2:C2 and fill down as far as needed.

Thank you so much, I have entered that and it works perfectly.

Thank you!!