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

 

[SkipVought]

Hi,

Do me a favor please. Select one of the cells with dates and change the number format to GENERAL. Tell me what jappens, please.

 

[zelgar]

The following should work (Note: In my formula, I had the calculations in Cells I10 & J10):

=IF(DAY(A3)=15,A3,IF(DAY(A3)>15,DATE(YEAR(A3),MONTH(A3),15),EDATE(DATE(YEAR(A3),MONTH(A3),15),-1)))
=EDATE(B1,-1)

You'll need to change the number formatting to get the date format you want.

 

[pendle666]

Hello SkipVought

I've formatted the H10 cell and it's come up with 42000 (the date is 27/12/14).



thank you for helping

____________
Pendle

 

[pendle666]

Hello Zelgar

I have tried your formula and it has worked perfectly. Thank you very much.

Can I bother you for something else similar?

If the result which is now sitting in L24 is 15/12/14, I now need to count back 8 weeks and come forward 1 day and pick the 15th of the month nearest to that 8 weeks.

Is that doable too?

Thank you so much for your help!

thank you for helping

____________
Pendle

 

[SkipVought]

Say you did a formula in Z24

z24: =L24-(8*7)+1

That's the day one day forward of 8 weeks back.

But I'm not sure what your next requirement is. Say that Z24 ends up as Feb 16. Do you want Feb 15 or Mar 15?

 

[zelgar]

The code gets a little long, but this should work:

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

 

[Andrzejek]

pendle666, to show appreciation for the help received, please use: [blue]
Like this post?
Star it![/blue]
At the bottom of the helpful post.
The little pink star which marks that post serves at least 2 purposes: it says “Thank you” and also let others know which post was helpful.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[pendle666]

Hello all,

Back to working on this after a 2 week holiday break.

Zelgar: the task is to "count back to the payday at least 8 weeks from teh date at L24 and come forward one day". So the date in L24 is currently 15/12/14. 8 weeks back would be 20/10/14, the nearest payday is 15/10/14 so a day forward would be 16/10/14.

Hope that makes sense!

thank you for helping

____________
Pendle

 

[zelgar]

My formula only needs to be slightly modified. Only the "15" for the True/False results need to be changed to a "16".

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

 

[pendle666]

You are a star Zelgar! Thank you ever so much!



thank you for helping

____________
Pendle

 

[zelgar]

Glad I could help.

 

[dhookom]

Skip, Zelgar, and other Excel gurus,

It would take me hours to figure out how Zelgar's recent expression works. Do you ever create your own function for this type of calculation since:
- the function might require only one or two arguments (cell references)
- it could easily be used anywhere in the workbook
- it could be documented (unlike the monstrous expression currently being used)
- it could be changed by placing some constants near the top of the function or possibly using global variables or values from cells
- the function could be named something like MaternityLeaveEndDate() or similar



Duane
Hook'D on Access
MS Access MVP

 

[pendle666]

Hello

These two formulae are working just fine:

=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))

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


However, they have the #VALUE error message when the cells L22 and L23 are empty. I'd like to have them blank if there's nothing in there and I've tried using =IF(ISERROR which I've done here:

=IF(ISERROR(L14-(7*25)),"",(L14-(7*25)))

but Excel won't accept it - can someone put me right please?

Many thanks

thank you for helping

____________
Pendle

 

[SkipVought]

@Duane, absolutely and often!

 

[zelgar]

Just add an =IF(L22="","",...) and =IF(L23="","",...) where ... is the original formula without the equal sign "=" at the beginning of the formula.

 

[pendle666]

Hi Zelgar

This is what I tried and it just doesn't like it:

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

That is the formula currently sitting in L23, but I've got #VALUE




thank you for helping

____________
Pendle

 

[zelgar]

It sounds like the cells (e.g., L22) aren't empty, but may contain a space or something. you can either try and delete the contents in the cell or maybe use the following code:
=IF(ISNUMBER(L22), ..., "")

This should work if the cells are blank or contain text.

 

[pendle666]

Ah yes, that it is. L22 contains a formula =L14 when I took out the formula the #value went away - I didn't realise that that error message came up if there was formula in the offending cell.

I shall try the ISNUMBER.


thank you

thank you for helping

____________
Pendle

 

[pendle666]

Oh Zelgar, can I be a complete pain and ask you to paste the entire string I need to use? I still can't get this to work properly and I'm sure I'm putting everything in the right place.



thank you for helping

____________
Pendle