Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Another MS Excel Question!

  • 09-08-2010 03:00PM
    #1
    Missy86
    Registered Users, Registered Users 2 Posts: 96 ✭✭


    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.
    Tagged:


Comments

  • #2
    dilallio
    Registered Users, Registered Users 2 Posts: 2,728 ✭✭✭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.


  • #3
    Missy86
    Registered Users, Registered Users 2 Posts: 96 ✭✭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".


  • #4
    Edser
    Registered Users, Registered Users 2 Posts: 398 ✭✭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


  • #5
    Missy86
    Registered Users, Registered Users 2 Posts: 96 ✭✭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?


  • #6
    Missy86
    Registered Users, Registered Users 2 Posts: 96 ✭✭Missy86


    Sorry - forgot to attach it!


  • Advertisement
  • #7
    Edser
    Registered Users, Registered Users 2 Posts: 398 ✭✭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..


  • #8
    Missy86
    Registered Users, Registered Users 2 Posts: 96 ✭✭Missy86


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


Advertisement