dates, 1 month prior

W

wfactor

Hi all,

examples of what I need, can someone assist?

Date provided = 21/05/06
I need excel to provide the date: 22/04/06

Date provided = 31/07/06
I need excel to provide the date: 01/07/06

Date provided = 01/06/06
I need excel to provide the date: 02/05/06


Thanks
 
M

Max

Presuming that, for consistency with the other 2 examples,
the date needed in your lines:
Date provided = 31/07/06
I need excel to provide the date: 01/07/06
Click to expand...

is 02/07/06

then this would suffice ..

Assuming source dates are in A1 down
In B1: =DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)+1)
Copy B1 down
 
W

wfactor

it doesn't fully work..

march 28 - 31
if the provided date is the 31st, and the month prior doesn't have 31
days then it also causes the wrong new date to show

provided date your formula date
4/01/2007 5/12/2006 (correct)
1/01/2007 2/12/2006 (correct)
31/03/2007 4/03/2007 (should be 01/03/07)
31/12/2006 2/12/2006 (should be 01/12/06)
 
M

Max

it doesn't fully work..

Yes, but that was the stated presumption / caveat in the earlier response ..

Try instead in B1, copied down:
=IF(DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)=DATE(YEAR(A1),MONTH(A1)+1,1),DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)+1))
 
A

Ardus Petus

=IF(DAY(A1)>DAY(DATE(YEAR(A1),MONTH(A1),0)),DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)+1))

HTH
 
M

Max

Ardus Petus said:
=IF(DAY(A1)>DAY(DATE(YEAR(A1),MONTH(A1),0)),DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)+1))
Click to expand...

Think the above seems to fail with, say: 30 Apr 2006 (last day of the month,
irrespective). The formula returns: 31 Mar 2006 (instead of 1 Apr 2006) in my
tests here. Same error results as well if it's 28 Feb 2006 (returns 29 Jan
2006, instead of 1 Feb 2006). I'm of course extending the interp on the OP to
assume that OP wants it to behave like that for the last day of the month,
irrespective of how many days the month may have.
 
Top