Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    HolidayPlanner - DeductDays (2003 SP2)

    Good Morning

    If I have dates in a row (for example Jan-08 = D6 = 1 through to AH6 = 31) and I want to put the number of holiday day entitlements in AI6, for example 25 what formula could I show in AJ6 to say that for every instance an H appears in D6:AH6 this number will be reduced by 1, for example if 7 days are taken and an H is placed in 7 cells AJ6 would reduce to 18.

    It is my intention to then have 11 other worksheets Feb through to December and I thought that for Feb's D6 I could start with =January!D6 and for March =February!D6 therefore reducing the entitlement through the year until it has all been used.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HolidayPlanner - DeductDays (2003 SP2)

    <code>=A16-COUNTIF(D6:AH6,"H")</code>

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayPlanner - DeductDays (2003 SP2)

    Thanks Hans

    A little more advice if you would not mind

    I have decided to have a Master page with all of the employee's names and their total yearly holiday allowance, this starts in cell B6 in C6 I would like to total the days taken so I tried this =SUM(January!($C$6:$AH6) which is fine if I have used numerical values (if I enter a 1 in Januart 2nd, 3rd and 4th it shows 3) which I can then deduct from B6 in D6 but I cannot get it to work if I use a letter 'H' I have tried to incoperate the Countif you showed me in the previous example thinking this would help but it just produces errors.

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayPlanner - DeductDays (2003 SP2)

    Hi

    I think I have solved it by making adding 12 columns in my MasterPage Jan-Feb and then in for example January I have put =January!AH6 etc. and then made a total column which I can deduct from the allowable total, this gives me an advantage as I can see at a glance the busy months on an 'overview' basis.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •