Another MS Excel Question!

Missy86

I downloaded a staff holidays template off the net, (see attached) I just have a question on a formula, so if one of you lovely boardsies would mind having a look I would be eternally grateful

In Cell I2, It has P = Public Holiday. I wanted to change this to B=Bank Holiday, but then I saw that the formula below is in the mon-fri cells.

=IF(OR(WEEKDAY($D17)=1,WEEKDAY($D17)=7),"W",IF($A17="P","P",""))

Can I just change the "P"'s to "B"'s and carry on?

I'm familiar with excel but I only have basic experience and this template is excellent for keeping track of staff holidays so I want to ensure i am using it correctly.

Cheers.

dilallio

You need to put a P in col A beside any day that's a public or bank holiday. The worksheet will then calculate everything correctly.

Missy86

Yea but if I change that to a "B" it still doesn't change the formula in the weekday cells. Its still coming up as a "P".

Edser

Yes, it will work if you change the two 'P's' in the formula to 'B's', eg. =IF(OR(WEEKDAY($D19)=1,WEEKDAY($D19)=7),"W",IF($A19="B","B","")).

If you then put a 'B' into Column A It will mark it as B (Bank holiday).

Note, there is also conditional formatting on the cells which turn the cells blue if it's a public/bank holiday. You will need to update this to change colour if cell value is 'B'.


Ed

Missy86

Would you mind looking at the attachment? I've done what you said and want to make sure it is right.

I've also change the conditional formatting - are you only allowed format three things? Like can I not have conditional formatting for all of them?

Missy86

Sorry - forgot to attach it!

Edser

Couple of things:

- You hadn''t updated headline to 'B = Bank holiday'
- cells E17 & F17 seemed to be blank?
- all cells weren't updated with new formula, eg. E14 to J25 still had 'P's'
- Blue conditional formatting didn't seem to be working for bank holidays.

Updated workbook attached..

Missy86

Great thanks Edser, god there is so much more to excel, I must only use about 2% of it on a daily basis.