Date range in 2 cells to give a "true" this cell if not 0

[Mark Liversidge]

Hi Everyone

Thank you for allowing me to joint this forum as I am stuck!

I have a sheet which has a start date in Contract start date D3 and an end date in E3.
Each picks up a query start and end date date in cell U1 and U2 respectively. Basically what I am trying to do is if the date in Cell D3>=U1 and the date in Cell E3<=E3 then return cell T3 and if not in range 0

I have written this =IF(AND(D3>=U1,E3<=U2),T3,0) which returns 0 but should be cell T3

I hope that this makes sense and would welcome any help offered

Regards
Mark

 

[Marc L]

Hi !

Welcome to this forum !

Seems some dates are not real date but text !

If not : « No arm, no chocolate ! » (no sample workbook attached, no answer …)
​

 

[Mark Liversidge]

Hi !

Welcome to this forum !

Seems some dates are not real date but text !

If not : « No arm, no chocolate ! » (no sample workbook attached, no answer …)
​

Thank you for your prompt reply

I have checked dates and all date format. I have Loaded relevant part of spreedsheet

Thanks Mark

 

[David Evans]

Thank you for your prompt reply

I have checked dates and all date format. I have Loaded relevant part of spreedsheet

Thanks Mark

Your formula in U3 is evaluating according to your formula - use the evaluate formula to verify this for yourself. I think the logic needs a rethink

 

[Mark Liversidge]

Thank you for your input its much appreciated. Back to the drawing board on the logic Thanks again, Mark

 

[David Evans]

Thank you for your input its much appreciated. Back to the drawing board on the logic Thanks again, Mark

I *suspect* that the formula in U3 should be =IF(AND(D3<=$U$1,E3>=$U$2),T3,0) - it gets you the result you want .

Otherwise, the only other room for "error" in that formula is using AND instead of OR - If I had a £10 for every time I've done that .... I haven't tried that to be honest

 

[Marc L]

Mark, "0" result is correct with your actual formula …
As Excel respects logic, so it's your bad !

Try this formula in U3 cell : =IF(AND(U$1>=$D3,U$2<=$E3),$T3,0)
In fact same logic as David !
​

 

[Mark Liversidge]

Mark, "0" result is correct with your actual formula …
As Excel respects logic, so it's your bad !

Try this formula in U3 cell : =IF(AND(U$1>=$D3,U$2<=$E3),$T3,0)
In fact same logic as David !
​

Thank you so much

 

[Mark Liversidge]

I *suspect* that the formula in U3 should be =IF(AND(D3<=$U$1,E3>=$U$2),T3,0) - it gets you the result you want .

Otherwise, the only other room for "error" in that formula is using AND instead of OR - If I had a £10 for every time I've done that .... I haven't tried that to be honest

Thank you again for your help much appreciated. In the main this works but if you look at the row highlighted yellow in the attachment that seems wrong. Any ideas please

Cheers
Mark

 

[Marc L]

No 'cause U2 is not <= to E13 !

Excel cannot be wrong, it just follows your logic …

So as David yet wrote, instead of AND maybe your logic needs an OR ?
​