Help Calculating Months -3+5

tshu44

New Member
Joined
Jan 17, 2014
Messages
2
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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Last edited:
Upvote 0
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! ....
[TABLE="width: 549"]
<tbody>[TR]
[TD]Temp PTO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee's Name[/TD]
[TD]Hire Date[/TD]
[TD]Today's Date[/TD]
[TD]PTO Used[/TD]
[TD]PTO[/TD]
[/TR]
[TR]
[TD]ALEXANDER, Shaunda[/TD]
[TD]11/21/2013[/TD]
[TD]1/19/2014[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BATES, AARON[/TD]
[TD]9/20/2013[/TD]
[TD]1/19/2014[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BLACKWOOD, JACOB[/TD]
[TD]10/24/2013[/TD]
[TD]1/19/2014[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]
 
Upvote 0
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
ABCDE
1Employee's NameHire DateToday's DatePTO UsedPTO
2ALEXANDER, Shaunda11/21/20131/19/201400
3BATES, AARON9/20/20131/19/201405
4BLACKWOOD, JACOB10/24/20131/19/201400
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,223,053
Messages
6,169,831
Members
452,284
Latest member
TKM623

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top