SOLVED

IF AND formula isnt working properly to find date between dates

%3CLINGO-SUB%20id%3D%22lingo-sub-3129569%22%20slang%3D%22en-US%22%3EIF%20AND%20formula%20isnt%20working%20properly%20to%20find%20date%20between%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3129569%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all!%20My%20IF%20AND%20formula%20isn't%20working%20properly%20when%20trying%20to%20find%20if%20a%20date%20is%20between%20my%20start%20and%20complete%20date%20and%20it's%20driving%20me%20crazy.%20Can%20anyone%20help%20please%3F%20I've%20attached%20the%20dataset.%20I'm%20trying%20to%20use%20it%20to%20make%20a%20Gantt%20chart%20but%20I%20can't%20even%20get%20it%20working%20X_X%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20current%20formula%20is%3A%3C%2FP%3E%3CP%3E%3DIF(AND(%24D%241%26gt%3B%3DA3%2C%20%24D%241%26lt%3B%3DB3)%2C%20%22Yes%22%2C%20%22No%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20keeps%20returning%20No%20when%20the%20referenced%20date%20IS%20between%20the%20start%20and%20complete%20date%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20all!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3129569%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3129718%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20AND%20formula%20isnt%20working%20properly%20to%20find%20date%20between%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3129718%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1297089%22%20target%3D%22_blank%22%3E%40keltzjd%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20D1%20you%20have%20text%20which%20only%20looks%20like%20date.%20Dates%20in%20Excel%20are%20actually%20numbers%20and%20any%20text%20is%20always%20%22more%20than%22%20any%20number.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3129756%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20AND%20formula%20isnt%20working%20properly%20to%20find%20date%20between%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3129756%22%20slang%3D%22en-US%22%3ESiiiiiiiigh%2C%20so%20much%20headache%20for%20such%20a%20simple%20mistake.%20Thank%20you%20sir%20X_X%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello all! My IF AND formula isn't working properly when trying to find if a date is between my start and complete date and it's driving me crazy. Can anyone help please? I've attached the dataset. I'm trying to use it to make a Gantt chart but I can't even get it working X_X 

 

My current formula is:

=IF(AND($D$1>=A3, $D$1<=B3), "Yes", "No")

 

It keeps returning No when the referenced date IS between the start and complete date

 

Thank you all!

 

 

3 Replies
best response confirmed by keltzjd (Occasional Contributor)
Solution

@keltzjd 

In D1 you have text which only looks like date. Dates in Excel are actually numbers and any text is always "more than" any number.

Siiiiiiiigh, so much headache for such a simple mistake. Thank you sir X_X