Results 1 to 2 of 2

Thread: Formula for tracking annual leave in Microsoft Excel

  1. #1
    Join Date
    Dec 2011
    Posts
    69

    Formula for tracking annual leave in Microsoft Excel

    Hi.

    I have a table listing the attendance of workers. Their attendance is logged using a series of codes, 1 for example is showing they turned up for work. There is another "PH" (public holiday) Leave which is inserted in their row if they take a PH leave day during that month. Each person has Ph Leave days allocated and what I need do is to be able to list down the dates when someone takes a PH leave in their PH leave table, with each date listed below one other. Hope I have explained this properly and would appreciate any help.

  2. #2
    Join Date
    May 2011
    Posts
    523

    Re: Formula for tracking annual leave in Microsoft Excel

    Try the below formula if the assumptions were
    - the codes are in B2:AH2
    - the dates are in B1:AH1

    Code:
    =IF(ISERROR(TRANSPOSE(SMALL(IF($B$2:$AH$2=A10,COLU MN(B2:AH2)-1,""),COLUMN(B2 :AH2) 1))),"",INDEX($B$1:$AH$1,TRANSPOSE(SMALL(IF($B$2:$ AH$2=A10,COLUMN(B2:A H2)-1,""),COLUMN(B2:AH2)-1))))
    Put PH in A10, and then select the maximum number of cells per leave, hit F2, enter the formula. It is an array formula, so commit with Ctrl-Shift Enter. Hope this helps.

Similar Threads

  1. Looking for Loan Payment Tracking Template for Microsoft Excel
    By Ramanujan in forum MS Office Support
    Replies: 2
    Last Post: 25-01-2012, 05:05 PM
  2. Looking for a good Sales Tracking Template for Microsoft Excel
    By @nki$achi in forum MS Office Support
    Replies: 2
    Last Post: 24-01-2012, 02:19 PM
  3. I need a template for Employee annual leave tracking
    By aMADeO! in forum MS Office Support
    Replies: 2
    Last Post: 11-01-2012, 04:49 PM
  4. Replies: 4
    Last Post: 06-01-2012, 05:53 PM
  5. Annual leave planner in ms excel 2003
    By Indrayan in forum Windows Software
    Replies: 4
    Last Post: 16-07-2011, 07:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,661,543.80643 seconds with 18 queries