+ Reply to Thread
Results 1 to 12 of 12

Leave tracker - how to use formula to automate the total leave days

  1. #1
    Registered User
    Join Date
    05-14-2018
    Location
    Hong Kong
    MS-Off Ver
    MS 365
    Posts
    44

    Leave tracker - how to use formula to automate the total leave days

    I create leave tracker

    List of employee List of Employee.png
    Leave tracker (listing of all staff's leave) Leave Tracker.png
    Year-to-date summary Year-to-date summary.png


    Currently, I use the pivot table to get the total days of each type of leave for employees and then manual input the total date of annual leave to the Year to date summary.

    I want to set up a formula that can automatically summing up the Total Annual leave taken according to "leave tracker"

    Should I still use the pivot table as a tool?

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,088

    Re: Leave tracker - how to use formula to automate the total leave days

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    05-14-2018
    Location
    Hong Kong
    MS-Off Ver
    MS 365
    Posts
    44

    Re: Leave tracker - how to use formula to automate the total leave days

    I guess I make it correctly. I have upload the file. It seems the formula at tab "year-to-date summary" the total annual leave or sick leave do not set up properly. I use Vlook up.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,088

    Re: Leave tracker - how to use formula to automate the total leave days

    In G5

    =IF($A5="","",VLOOKUP([@Name],'Leave Tracker'!$J$4:$P$8,2,0))

    in H5

    =IF($A5="","",VLOOKUP([@Name],'Leave Tracker'!$J$4:$P$8,4,0))

  5. #5
    Registered User
    Join Date
    05-14-2018
    Location
    Hong Kong
    MS-Off Ver
    MS 365
    Posts
    44

    Re: Leave tracker - how to use formula to automate the total leave days

    Thanks John.

    But we have half day leave which is not calculated base on this formula.
    Also, I would like to know what does the meaning of A5=""? A5 is name so why to show "" ?

  6. #6
    Registered User
    Join Date
    05-14-2018
    Location
    Hong Kong
    MS-Off Ver
    MS 365
    Posts
    44

    Re: Leave tracker - how to use formula to automate the total leave days

    I find that if I adjust the date format like this - 12/2/2018 8:30:00 AM

    If the format is change with time, is it helpful for formula setting for annual leave calculation. Now another question is : how can I set the leave type for the half day? Should I set up the working hour in the leave type ?

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,088

    Re: Leave tracker - how to use formula to automate the total leave days

    In "Leave Tracker"

    In G5

    =SUM(SUMIFS('Leave Tracker'!$F:$F,'Leave Tracker'!$B:$B,$A5,'Leave Tracker'!$E:$E,{"Annual Leave","Annual Leave (Half Day)"}))

    in H5

    =SUM(SUMIFS('Leave Tracker'!$F:$F,'Leave Tracker'!$B:$B,$A5,'Leave Tracker'!$E:$E,{"Sick Leave","Sick Leave (Half Day)"}))


    You do not need the table in J:P of "Leave tracker"


    FYI:

    =IF($A5="","",VLOOKUP([@Name],'Leave Tracker'!$J$4:$P$8,4,0))

    This to avoid having N/A# error showing when you drag the formula down a column and there are no names

    an alternative is ..

    =IFERROR(VLOOKUP([@Name],'Leave Tracker'!$J$4:$P$8,2,0),"")
    Attached Files Attached Files
    Last edited by JohnTopley; 05-16-2018 at 01:45 AM.

  8. #8
    Registered User
    Join Date
    05-14-2018
    Location
    Hong Kong
    MS-Off Ver
    MS 365
    Posts
    44

    Re: Leave tracker - how to use formula to automate the total leave days

    It works. Thanks for all answers.

    Last but not least, if set up the leave tracker date with time, for example: Start Date 12/2/2018 8:30:00 AM . End Date 12/2/2018 12:30:00 PM, this is half day of leave. As such, it can calculate the days taken based on date & time. Is that feasible? How will be the formula on F column at leave tracker? Should I set up working hours criteria at the leave type tab?

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,088

    Re: Leave tracker - how to use formula to automate the total leave days

    You can do a time based calculation: what about a half day taken in the morning i.e start work at (say) 13:00?

    How are start/end dates/times recorded?

    This would most likely changing column E from a drop-down selection of "Leave type" to formula. Is it worth the effort ?

  10. #10
    Registered User
    Join Date
    05-14-2018
    Location
    Hong Kong
    MS-Off Ver
    MS 365
    Posts
    44

    Re: Leave tracker - how to use formula to automate the total leave days

    Start Date 12/2/2018 8:30:00 AM . End Date 12/2/2018 1:00:00 PM . (=4.5 hours=half day)

    =IF(end date-start date>4.5,IF(end date-start date<=4.5,0.5,1),1)

    I set formula like this but it shows 1 always. Is any error of my formula? By the way, if the end date is 14/2/2018 1:00:00PM and start date is the same as above (=2 days 4.5 hours) it shows 1 too.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,088

    Re: Leave tracker - how to use formula to automate the total leave days

    See attached:

    in C2

    =INT(B2)-INT(A2)+IF(MOD(B2,1)-MOD(A2,1)>4.5/24,1,0.5)
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-14-2018
    Location
    Hong Kong
    MS-Off Ver
    MS 365
    Posts
    44

    Re: Leave tracker - how to use formula to automate the total leave days

    My chart is perfect now. I have learned a lot here. Thank you so much for your great help, John.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Leave Tracker
    By markusvirus in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2017, 02:07 AM
  2. Formula Help for Annual Leave Tracker
    By Roquet_Man in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-11-2017, 01:47 PM
  3. Help build a Leave tracker and Effort Time Estimation Tracker
    By cherias in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2015, 02:10 PM
  4. Need only leave days one by one from leave attendace full data
    By sathiyamoorthy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2014, 03:23 AM
  5. Calculate total working days and excepted leave days
    By megaiooo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-21-2013, 09:29 AM
  6. Leave Tracker
    By liarliar in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-24-2008, 01:00 PM
  7. leave tracker
    By jprakash4u in forum Excel General
    Replies: 2
    Last Post: 03-25-2008, 12:32 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1