Oct 31 2023 04:12 AM
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 date | LOS Formula |
11/06/2012 | 11 |
12/09/2022 | 1 |
31/07/2023 | <1 |
13/02/2017 | 6 |
20/03/2023 | <1 |
07/02/2022 | 1 |
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) |
<1 | 1 | 0 |
1 | 1 | 2 |
2 | 2 | 4 |
3 | 3 | 6 |
4 | 6 | 12 |
5 | 10 | 16 |
LOS Formula | Entitlement Full Pay | Entitlement Full Pay Formula | Entitlement Half Pay | Entitlement Half Pay Formula |
11 | 10 | 10 | 16 | 16 |
1 | 1 | 1 | 2 | 2 |
<1 | 1 | 0 | ||
6 | 4 | 3 | 6 | 6 |
<1 | 1 | 0 | ||
1 | 1 | 1 | 2 | 2 |
Oct 31 2023 05:52 AM - edited Oct 31 2023 05:52 AM
@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.
Oct 31 2023 05:56 AM
Oct 31 2023 06:03 AM
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.