IF statements and dates - if this date is before that date put this date 2

[pendle666]

Hello

I'm in a muddle with using dates in Excel once more.

I'm doing a maternity leave calculator. I have my date in cell H10, 27/12/14, which has been calculated from elsewhere.

In my next two cells, I want to put the two paydays previous to 27/12/14 - the answer to which is 15/12/14 and 15/11/14 because we're paid on 15th of the month.

If the contents of H10 was 13/10/14 then the two required dates would be 15/09/14 and 15/08/14.

I hope what I'm trying to do makes sense and is doable.

Thank you in advance.

thank you for helping

____________
Pendle

 

[zelgar]

Try this...

=IF(ISNUMBER(L22),IF(DAY(L22)=15,L22,IF(DAY(L22)>15,DATE(YEAR(L22),MONTH(L22),15),EDATE(DATE(YEAR(L22),MONTH(L22),15),-1))),"")

=IF(ISNUMBER(L23),IF(ABS(DAY(L23-8*7+1)-15)>=EOMONTH(L23-8*7+1,0)-L23-8*7+1+15,DATE(YEAR(L23-8*7+1),MONTH(L23-8*7+1),16),DATE(YEAR(L23-8*7+1),MONTH(L23-8*7+1)+1,16)),"")

 

[pendle666]

You star! It's worked Thank you so much once again.

thank you for helping

____________
Pendle