# Calculating vacation entitlement with IF function while using a DATEDIF formula

#### LaRina86

##### New Member
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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 ?

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.

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)

SOLVED!

etaf, you rock. I didn't even realize there was a space there. 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.

you are welcome

Replies
17
Views
383
Replies
1
Views
70
Replies
1
Views
311
Replies
2
Views
203
Replies
7
Views
165

1,196,483
Messages
6,015,460
Members
441,897
Latest member
erma

### 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.

### Which adblocker are you using?

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

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