Staff holiday planner

Hello,

I am trying to make a staff holiday planner, but not very good with excel. I know what I want, but cant work out how to get it.

This is a screen shot of what I have:

What I am after is every time I put H in a date, it deducts 1 from the remaining holiday number. Please can anyone tell me how to do that?

Thank you in advance!

Attachment 480524

Attachment is invalid.

Post a sample Excel file (not image) showing sample results by clicking "Go Advanced" then scroll down to "Manage Attachments"

Oh, screenshot didn't work.

I have staff name in column A, number of holiday days in column B, and then each column after that each column is 1 jan, 2 jan, 3 jan and so on.

What I want, is every time I put H (to represent a days holiday) in a cell in a row that corresponds to a member of staff, I want the figure in their holiday number of holidays left, to reduce by 1.

I hope that makes sense?

How do I do a sample excel file

With "Holiday Allowance" in B2

"Holiday Balance" in C2

Date in D2 onwards

in C2

=$B2-COUNTIF($D2:$BB2,"H") -- Change COUNTIF range as needed

That's amazing, thank you!

Now... is possible to also have the same thing happen but for half a day holiday (HB) deducting 0.5 of a day from the "holiday balance", how to I separate the two formulas in the same cell?

=$B2-COUNTIF($D2:$BB2,"H")-(COUNTIF($D2:$BB2,"HB")/2)

This just adds all the HB as 1 each and divides by 2. Hope that helps

You are an absolute legend!

And one last question. I have merged the cells above the dates to show the months, e.g A1-A31 is merged and says January. I have defined the name for these merged cells and in the dropdown name box in the top left I can quick navigate to that month.

Call me picky, but I want to change the order so the months don't appear in alphabetical order, and are in month order. Is that doable?