Checking Multiple conditions

[Desiturtle]

I have to check multiple conditions to return a specific value.

If between two date values + between two time values + between two day values --->all are true then return a value else a different value...I cant get to do it with if and AND as it gets too confusing.

Any simple formula to do this please?

 

[Ramesh]

Can you share any sample data that would help in understanding the requirement..

 

[Desiturtle]

Can you share any sample data that would help in understanding the requirement..

Thanks , I have a uploaded the file I was trying to work with.

 

[Hui]

G2: =IF(AND(B2>=DATE(2017,1,1),B2<=DATE(2017,8,30),C2>=1,C2<=5,D2>TIME(8,0,0),D2<=TIME(17,30,0)),1.5,1)
Copy down

 

[Desiturtle]

Thanks Hui...this works perfect...
any other function that I can use...I will try to work on that if you suggest.

 

[Hui]

Why, It does what you want ?

Thanks Hui...this works perfect...
any other function that I can use...I will try to work on that if you suggest.

 

[NARAYANK991]

Hi ,

Excel generally has many ways of doing the same thing ; here is one other way of doing what you want :

=IF(COUNTIFS(B2,">=01-01-2017",B2,"<=30-08-2017",C2,">=1", C2,"<=5", D2, ">=08:00:00", D2, "<=17:30:00"), 1.5, 1)

Narayan

 

[Hui]

How about using NO functions?
G2: =1+(B2>=DATE(2017,1,1))*(B2<=DATE(2017,8,30))*(C2>=1)*(C2<=5)*(D2>TIME(8,0,0))*(D2<=TIME(17,30,0))*0.5

 

[Desiturtle]

Hi ,

Excel generally has many ways of doing the same thing ; here is one other way of doing what you want :

=IF(COUNTIFS(B2,">=01-01-2017",B2,"<=30-08-2017",C2,">=1", C2,"<=5", D2, ">=08:00:00", D2, "<=17:30:00"), 1.5, 1)

Narayan

Thanks...it helps !

 

[Desiturtle]

How about using NO functions?
G2: =1+(B2>=DATE(2017,1,1))*(B2<=DATE(2017,8,30))*(C2>=1)*(C2<=5)*(D2>TIME(8,0,0))*(D2<=TIME(17,30,0))*0.5

Thanks...was just looking at different ways to do it.