If/Or formula

MJLG85

New Member
Joined
Jan 24, 2018
Messages
40
[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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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...
 
Upvote 0
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.
 
Last edited:
Upvote 0
Re: If/Or formula Help

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

[TABLE="width: 919"]
<tbody>[TR]
[TD][/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]
[TD]N[/TD]
[/TR]
[TR]
[TD]2[/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]3[/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/25[/TD]
[TD]2712[/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))

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
 
Upvote 0
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
 
Upvote 0
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)...
Code:
=J3+IF(H3<C3-B3+1095,0,IF(H3>C3-B3+1460,2,1))
</c3-b3+1095,0,if(h3>
 
Upvote 0
Re: If/Or formula Help

Thank you [MENTION]Joe4[/MENTION]!

= J3 + IF ( H3 < C3 - B3 + 1095 , 0 , IF ( H3 < C3 - B3 + 1460 , 1 , 2 ) )
 
Last edited:
Upvote 0
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.
Code:
[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]
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top