If/Or formula

[MJLG85]

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Start date

Year End of 1st Year

Hol Year start

Hol Yr End

Accrual Start Date

Today

Total Days in Service

Accrued Holiday

Holiday Entitlement

Taken Holiday Days

Taken Half Holiday Days

Taken Holiday Days

Remaining Holiday Days

2

20/11/17

31/03/2018

31/03/19

20/11/17

24/04/19

520

20

0.0

0.0

0.0

20.0

<tbody>
</tbody><colgroup><col><col><col span="2"><col span="3"><col><col span="6"></colgroup>

=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

 

[stevieinselby]

Re: If/Or formula Help

So if H3 is less than C3-B3+1095 then =J3, otherwise =J3+1 if over 1095 or J3+2 if over 1460?

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

What do you want to happen when H3=C3-B3+1095? At the moment, you don't appear to have that case covered...

 

[Special-K99]

Re: If/Or formula Help

You don't need all those brackets neither the OR.
Looks like you've left out a closing bracket in the C3-B3+1460 comparison.
If H3>(C3-B3)+1460 it must therefore be greater than (C3-B3)+1095 but it wont get as far as the 1460 comparison because of you 1095 comparison. Therefore you'll get the wrong result.

I'm pretty sure it should really be this (in this order).

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

What happens if H3 EQUALS (C3-B3)+1095 ?

It may be better if you explain in words what you're trying to achieve.

 

[MJLG85]

Re: If/Or formula Help

I'm an idoit... that table columns and rows numbers are wrong which is throwing everything out

B

C

D

E

F

G

H

I

J

K

L

M

N

2

Start date

Year End of 1st Year

Hol Year start

Hol Yr End

Accrual Start Date

Today

Total Days in Service

Accrued Holiday

Holiday Entitlement

Taken Holiday Days

Taken Half Holiday Days

Taken Holiday Days

Remaining Holiday Days

3

20/11/17

31/03/2018

31/03/19

20/11/17

24/04/25

2712

20

0.0

0.0

0.0

20.0

<tbody>
</tbody><colgroup><col><col><col span="2"><col span="3"><col><col span="6"></colgroup>

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

Breaking it down I need...
=IF(OR((H3<((C3-B3)+1095)),(J3),0) ... Basially to show the value of J3 (which will be 20)

IF((H3>((C3-B3)+1095)),(J3+1),0), ... will add 1 to J3

IF((H3>(C3-B3+1460)),(J3+2),0)) ... will add 2

I think i have been tampering too much and lost the later working

 

[MJLG85]

Re: If/Or formula Help

and I am trting to acheive the formuala that will say after 3 full years of service (plus what they working in the first part year they work) they get an additional days holiday, 4 years, 2 days, 5 years = 3 days, 6 years = 4 days, 7+ years = days.

I was just trying to achieve this for the first part before i moved on to adding 5,6,7 years

 

[stevieinselby]

Re: If/Or formula Help

So if H3 is less than C3-B3+1095 then =J3, otherwise =J3+1 if over 1095 or J3+2 if over 1460?

How about:
=J3+IF(H3<c3-b3+1095,0,if(h3>C3-B3+1460,2,1))

What do you want to happen when H3=C3-B3+1095? At the moment, you don't appear to have that case covered...

D'oh, it seems that the forum software has parsed my less thans and greater thans out and completely mangled it (either that or I mangled it myself and didn't notice)...

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

</c3-b3+1095,0,if(h3>

 

[Joe4]

Re: If/Or formula Help

D'oh, it seems that the forum software has parsed my less thans and greater thans out and completely mangled it (either that or I mangled it myself and didn't notice)...

Just surround them by spaces when typing them here.
See this: https://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

 

[stevieinselby]

Re: If/Or formula Help

Thank you [MENTION]Joe4[/MENTION]!

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

 

[Joe4]

Re: If/Or formula Help

Thank you @Joe4;!

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

You are welcome.

By the way, you only need to do it surrounding the greater than and less than signs (so that might save you a little time), i.e.

[COLOR=#333333]=J3+IF(H3 [/COLOR][COLOR=#ff0000]<[/COLOR][COLOR=#333333] C3-B3+1095,0 ,IF(H3 [/COLOR][COLOR=#ff0000]<[/COLOR][COLOR=#333333] C3-B3+1460,1,2))[/COLOR]

 

[MJLG85]

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?