Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Vacation Accrual/PTO Calculator in Excel

0

I would like to create an excel spreadsheet to automatically calculate how much PTO I have accrued based on my start date. I can then add a separate area for PTO taken and handle the subtraction myself. 

120hrs/year (10 hrs/month, 5 hrs/pay period)

Start Date: January 15th, 2020 

Answer
Discuss

Discussion

Hi and welcome to the forum.

Before we can asnwer that, please revise your question to clarify how the 120hr/year is accrued. Is it a) uniformly by calendar date and b) by the minute or hours?
John_Ru (rep: 6102) Oct 26, '21 at 12:54 pm
Hi John, for salaried employees, you receive 10hrs/month, 5hrs/pay period. You start accruing at the start date. 
mcomer Oct 26, '21 at 12:55 pm
Add to Discussion

Answers

0

Excel has a  function DATDIF (undocumented under Fx) which can do this. Just put this in a cell:

=10*DATEDIF(DATEVALUE("15/01/2021"),TODAY(),"M")

and change the date in bold to your locale/ 2020 if accrual in cumulative over years.The DATEDIF function returns the number of months since the first date(with hte argument "M") which is then multiplied bu 10 (hrs/month) to give the total accrued hours.

You will need to format that cell to General or it will appear as a date.

Alternatively try putting your start date in cell A1 and use this instead:

=10*DATEDIF(A1,TODAY(),"M")

Hope this helps.

Discuss

Discussion

Hi John, Thanks for your help but I receive a #Value! error with this formula. 
mcomer Oct 27, '21 at 11:34 am
Suggest you try putting your start date in cell A1 and use this instead:
=10*DATEDIF(A1,TODAY(),"M")
You will need to format the cell to General.

I've added the above to my Answer. Both work on a PC/ Excel 365 but should work in all versions..You can use "Select All" in the code extracts to copy that to the clipboard.
John_Ru (rep: 6102) Oct 27, '21 at 1:17 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login