Need Help with if statement involving two dates and a day of the month

[twinshot]

Hi there,

This is my first post in the forum and I have google to try and find a solution with no luck. I am trying to make a if statement that takes two dates (eg. Jan 1 2017 and February 12 2017). What I want to do with these two dates is if a 15th falls between these two dates I want a certain value to be chosen. I am trying to make a budget sheet. So like if There if a 15th falls between those two dates I want it to minus 1500 from the value of another cell. I already know how to take the value from another cell and subtract it. I just dont know how to get excel to see if a day of the month falls between two dates. Like the 1st, 15th or 31st. Also is there a way to make this formula check for more than one number. Like if the dates between have a 1st or a 2nd put 1500 if not put 2000.

I hope this makes sense and thanks in advance for the help.

Twinshot

[EchoPassenger]

To check if the day is a 15th and it's between two dates =AND(A1>=Jan 1 2016,A1<=Feb 12 2016,DAY(A1)=15)
- Obviously with the above date range there is only 'Jan 15 2016' between them, so you could just test test for that directly.
- Also note that the above date range is currently INCLUSIVE of the two dates.

[twinshot]

I tried to implement this in my sheet but I couldn't get it to work. Here is what I have on my page and am trying to work out.

	A	B	C	D
1	Start Date	End Date	Pay Amount	Amount After Deductions
2	9/9/2016	9/22/2016	1500	1000
3	9/23/2016	10/06/2016	1500	1300

So what I am trying to find out is the formula I would put into D2 and D3. If a 1st falls between A2 and B2 I want to subtract 200 from C2. If there is no 1st I want it to subtract 500.

I hope that makes more sense.

Thanks
Twinshot

[EchoPassenger]

Try =IF(OR(MONTH(B2)>MONTH(A2),DAY(B2)=1),"1st","")

That should cover both possibilities...

1) If the month in B2 is greater than the month in A2 - the implication being that there must be 1st between dates.
2) If the day in B2 is 1(st) - Just in case both dates are set to 1st.

Obviously replace "1st" with your intended calculation.

* Edited to shorten the formula

[twinshot]

OMG it worked! Thank you so much! I knew it was possible just didnt know how...

Thank you for your help EchoPassenger

[EchoPassenger]

I've just shortened the formula - please make sure you just update and test it in your file.

[twinshot]

Just replaced it. Thanks for the amazing work

[EchoPassenger]

Apologies, just realised I edited the wrong part of the Day argument.

Should be =IF(OR(MONTH(B1)<>MONTH(A1),DAY(A1)=1),"1st","")

I've also changed the Month comparison to <> just in case the month in B2 is before the month in A2, which shouldn't be the case but you never know.