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

1. ## 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?  Register To Reply

2. ## 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.  Register To Reply

3. ## 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.  Register To Reply

4. ## 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))  Register To Reply

5. ## 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 "" ?  Register To Reply

6. ## 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 ?  Register To Reply

7. ## 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),"")  Register To Reply

8. ## 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?  Register To Reply

9. ## 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 ?  Register To Reply

10. ## 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.  Register To Reply

11. ## 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)  Register To Reply

12. ## 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.  Register To Reply