If/Or formula

[MJLG85]

[TABLE="width: 919"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start date[/TD]
[TD]Year End of 1st Year[/TD]
[TD]Hol Year start[/TD]
[TD]Hol Yr End[/TD]
[TD]Accrual Start Date[/TD]
[TD]Today[/TD]
[TD]Total Days in Service[/TD]
[TD]Accrued Holiday[/TD]
[TD]Holiday Entitlement[/TD]
[TD]Taken Holiday Days[/TD]
[TD]Taken Half Holiday Days[/TD]
[TD]Taken Holiday Days[/TD]
[TD]Remaining Holiday Days[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20/11/17[/TD]
[TD="align: right"]31/03/2018[/TD]
[TD] [/TD]
[TD]31/03/19[/TD]
[TD]20/11/17[/TD]
[TD]24/04/19[/TD]
[TD]520[/TD]
[TD] [/TD]
[TD]20[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]20.0[/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col span="3"><col><col span="6"></colgroup>[/TABLE]



=IF(OR((H3<((C3-B3)+1095)),(J3),0),IF((H3>((C3-B3)+1095)),(J3+1),0),IF((H3>(C3-B3+1460)),(J3+2),0))


So I am now trying to get this formula to work correctly.

The second and third IF calculations work when the number is over the amount and it adds 1, 2. But I can't seem to get the first bit correct to show that when H3 is is less then (C3-B3)+1095 then it just needs to show J3

 

[Joe4]

Re: If/Or formula Help

1825 = +3, 2190 = +4, 2555 = +5

I am sensing a pattern here.
Instead of keeping adding if statements, why not just calculate it like:

=(value-730)/365

If you plug those numbers (1825, 2190, 25555) into value, you get 3,4,5.

 

[MJLG85]

Re: If/Or formula Help

With the risk of sounding a little stupid... but I don't actually get what you mean. I know when i start adding more IF formulas then it starts tell me about too few/many arguements.

 

[stevieinselby]

Re: If/Or formula Help

That's step one... thanks!

Now I just need it do.. the same thing but instead of 1460 part I need to have that after 1825 = +3, 2190 = +4, 2555 = +5

Is this too much?

You can keep nesting IF statements until the cows come home, but a more efficient way to do it would be:
=J3+ROUNDDOWN((H3-C3+B3-730)/365,0)
which is then infinitely extendable.
If you want to cap it at 5 extra days then you can do that with
=J3+MIN(ROUNDDOWN((H3-C3+B3-730)/365,0),5)

 

[MJLG85]

Re: If/Or formula Help

Thanks both of you!.

I think it is now working nicely!!

 

[MJLG85]

Re: If/Or formula Help

just when you thought you were done with me...

This seems to subtract days when it's less they the 3 years before it starts adding on the extra day

 

[stevieinselby]

Re: If/Or formula Help

Oops!
=J3+MAX(MIN(ROUNDDOWN((H3-C3+B3-730)/365,0),5),0)

Adding the MAX(...,0) around the formula will ensure that it doesn't give you a negative number.

 

[MJLG85]

Re: If/Or formula Help

That works perfectly...

Next question...do you think there is a way to do this... base C3 on what B3 is? so it works out to be the following March dependin whenever they start so I wouldn't have to input the year every time in C3?

 

[Joe4]

Re: If/Or formula Help

base C3 on what B3 is? so it works out to be the following March dependin whenever they start so I wouldn't have to input the year every time in C3?

Assuming your end date is always the 31st of March, try this formula in C3:

=DATE(YEAR(B3)+IF(MONTH(B3)>3,1,0),3,31)