Calculating vacation entitlement with IF function while using a DATEDIF formula

LaRina86

New Member
Joined
Dec 15, 2016
Messages
3
Hi all,

I've done my best to research my issue, but have so far come up blank and am hoping for some of your expert assistance. :)

I am having some trouble with using the IF function to calculate the vacation entitlement of employees with a certain tenure. I would like to stipulate that an employee whose tenure is less than 5 years would get 80 hours of vacation, but if 5 or more years, would get 120 hours of vacation.

On my spreadsheet, Column D displays the years of tenure. However, what I think is throwing a wrench into things is that the values in column D are not straight numbers I've typed in, but values I calculated using the DATEDIF function.

The below formula is what I've been using:
=IF(D2<5, "80", "120")

Now, the issue I'm running into is thatthe answer always ends up being 120, even if the value in column D is less than 5 years. I'm thinking this is because the data in D2 is not a straight number, but a calculation

Any assistance in helping me correct this would be greatly appreciated, and thank you in advance! If there's any other info I can provide, please let me know.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Are you using
[FONT=&quot]=DATEDIF (start_date, end_date, "Y")

also using "80" is putting text into the cell

[/FONT]
=IF(D2<5, 80, 120)

no reason why that should not be working - datedif does return a value to test

how is the cell D2 formatted ?
 
Upvote 0
Hi Etaf, thank you so much for your response. D2 is formatted in the following way:

=DATEDIF(B2,C2,"y")&" "

B2 is the employee's hire date and C2 is the current date, so DATEDIF in this case measures the employee's tenure in years by calculating the difference between the present date and their date of hire. All data in Column C (again, current date) is not straight typed, but a calculation using the =TODAY() function.
 
Upvote 0
why the " " space added ?
that makes the number text and not a number , hence why the IF does not work

=IF(D2*1<5, 80, 120)
will change D2 back into a value
OR
=IF(value(D2)<5, 80, 120)
 
Upvote 0
SOLVED!

etaf, you rock. I didn't even realize there was a space there. :oops: I removed that and went with the DATEDIF formula you originally indicated, =DATEDIF (start_date, end_date, "Y"), and it resolved everything.

Thank you so much again for the help! I hope you have a great day.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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