DAY value between two dates

[flashgordie]

Hello, I am trying to establish a formula that will determine if the DAY value will fall between two dates.

A1 = Start Date; B1 = March 28-13 cell formatted as date
A2= End Date; B2 = April 8-13 cell formatted as date
A3= DAY; B3 = 01 cell formatted as date but to only display the DAY as dd


In this case the formula would determine whether or not B3 falls between B1 and B2. In this case it does, as the first of the month (01) does indeed fall between the 28th and the 8th.

I realize the is probably a disconnect in cell B3 formatted as a date, and the fact that it is only the DAY that I want to look at, but if I knew had to work that I , I wouldn't need your help. So with that said, any assistance would be greatly appreciated!

Thank you!

 

[barry houdini]

To check that a date B3 is between B1 and B2 you can use a formula like this

=AND(B3>=B1,B3<=B2)

....of course that will only be TRUE here if the date is actually 1st April 2013 (not any other 1st of the month)

 

[flashgordie]

Thank for the reply, however, that is the problem, I am looking to use the DAY not the date. The date will change, but the day of the month will always be the first. So my problem is a formula that will use the DAY, Not the actual date

 

[barry houdini]

OK, for your example, assuming that any day from 28 to 8 will result in TRUE try this formula

=(DAY(B1)<=DAY(B3))+(DAY(B2)>=DAY(B3))+(DAY(B2)<=DAY(B1))=2

That's not looking at the dates, just the days - I'm assuming that the B1/B2 date range is always a maximum of a month long

If that doesn't do what you want can you provide some more examples?

 

[flashgordie]

Thanks BH, Thank you! Yes, your assumption of the date range being a maximum of a month long is correct. from a quick look, that looks more along the lines of what I am looking for. I transformed this into an "if" statement.

=IF(((DAY($F$3)<=DAY($H8))+(DAY($F$4)>=DAY($H8))+(DAY($F$4)<=DAY($F$3))=2),Data!E22,"---") ...but I still was wondering what the '=2' refers to, is that the true false condition? is this based on odd vs even for true and false?

Thanks for your help. I think I'm back on track now.