Formula issues, not calculating as wanted and not returning a value, instead showing blank.

Copper Contributor

I have spreadsheet that I am adding formula's in to calculate length of service and then further v lookup formulas to then pick up sick entitlements based off of the length of service. I have a couple of issues - 


The formula used to calculate length of service, is there a way to make it look at years only, not months? My formula is : =IF(DATEDIF($F14,TODAY(),"Y")<1,"<1",DATEDIF($F14,TODAY(),"Y"))

Continuous service dateLOS Formula


The next formula issue I have is in my spreadsheet I have a formulas that look at length of service calculated, then lookups to a table at the top of the spreadsheet to find LOS value, then select the sick entitlement for full pay and half pay. 

Tables shown below, Green is manually calculated values, red is where I have tried to get a formula to do it, you will see the blanks on the red columns where they should be returning a value (green), I believe this is to do with the <1 showing in LOS formula, is there a way to fix this still using the <1 in LOS?


Formula's are : 

=IFERROR(VLOOKUP($I14, $N$1:$P$7, 2, TRUE), "")

=IFERROR(VLOOKUP(I14, $N$1:$P$7, 3, TRUE), "")

=IFERROR(VLOOKUP($I15, $O$1:$Q$5, 2, TRUE), "")

=IFERROR(VLOOKUP(I15, $N$1:$P$5, 3, TRUE), "")

Service (years)Full Pay (weeks)50% (weeks)


LOS FormulaEntitlement         Full PayEntitlement Full Pay FormulaEntitlement       Half PayEntitlement Half Pay Formula
<11 0 
<11 0 


3 Replies

@SDTC2023 I believe you make it difficult for yourself by introducing "<1". Better to stay with numbers, and use 0 (zero) for an LOS less then 1.

Then I think you made an error in your example. Why would an LOS of 6 become 4 and 6. I would expect 10 and 16 as 6 years is greater than 5 years, being the maximum LOS in the lookup table.


Anyhow, the attached file contains some formula how I would deal with it.

The way the company has done this to date is to show LOS as <1 when under a year so they want to keep that format - I do understand this is probably causing issues with the formula working.

The difference in the calculation that I haven't explained fully is that some employee's sick entitlement stops at the LOS 3 years threshold, others get the 4 and 5 years threshold which is why I have the 4 different formula's at play in the sheet.

Thank you for sending over a sheet, I will have a look at this now and see if it fixes the issues!


With regard to the "<1" issue you can use a custom format 0;;"<1" to display zeros as <1, keeping intact the number 0.

Not really sure how to tackle the other issues, but I trust you'll figure that out yourself.