Help Calculating Months -3+5

[tshu44]

Thanks for the time in reading this. This is my first post. I am trying to figure out (without any success yet) on how to use excel to figure out my employes PTO. Personal Time Off. This is how it works. Employees must work for three months before getting 5 hrs of PTO. Each month after that they get 5 hrs added on to the first three months. So I was trying to look at excel sites and their are different way to do it. My speadsheet looks something like this:

Employee name Hire Date Today's Date PTO

Where I get confused is when excel is calculating the days, does it know how many days are in a certain month? Also, after looking at the different ways to construct the formula for this problem with no success, I'm about to give up.

What could be a formula that could work? I am not an excel expert, I'm leaving that to you I really appreciate any help I can recieve.

 

[AlphaFrog]

The DATEDIF function can calculate the number of months between dates regardless of the number of days in a month.

Try this where B2 is the Hire date

=MAX(DATEDIF(B2,TODAY(),"m")-2,0)*5

Excel may try to automatically set the cell with this formula as a date format. Just reset it to general or number cell format.

 

[tshu44]

Eh... no luck... I appreciate the quick response. Thank you. I pasted what I am trying to work with. 5 columns. I would have to automatically enter the PTO used. I'll keep trying. There has to be a way. Maybe you could take another look and have a suggestion?


PS. I told one of my co-workers about your site and she joined too! We really find your Blog helpful! ....

Temp PTO
Employee's Name	Hire Date	Today's Date	PTO Used	PTO
ALEXANDER, Shaunda	11/21/2013	1/19/2014	0
BATES, AARON	9/20/2013	1/19/2014	0
BLACKWOOD, JACOB	10/24/2013	1/19/2014	0

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

 

[AlphaFrog]

I don't understand what "no luck" really means. It doesn't describe anything that could help to diagnose what the problem may have been. What did you try? Did the formula error or return an in incorrect value; for all, for some?

I just plugged in the same formula as before with your recent data table and it seems to work. You didn't list the expected PTO results, so I cannot compare.


<br />

Book1
	A	B	C	D	E
1	Employee's Name	Hire Date	Today's Date	PTO Used	PTO
2	ALEXANDER, Shaunda	11/21/2013	1/19/2014	0	0
3	BATES, AARON	9/20/2013	1/19/2014	0	5
4	BLACKWOOD, JACOB	10/24/2013	1/19/2014	0	0
Sheet1

Cell Formulas
Range	Formula
E2	=MAX(DATEDIF(B2,TODAY(),"m")-2,0)*5-D2
E3	=MAX(DATEDIF(B3,TODAY(),"m")-2,0)*5-D3
E4	=MAX(DATEDIF(B4,TODAY(),"m")-2,0)*5-D4

The formula also now subtracts the PTO Used because that's a new addition to your data table.