Excel 2000 and date math
 

I am trying to allow a user to enter a date and then calculate the month
before.

Eg. Enter in Cell A1 3/31/2009
Cell A2 will calculate automatically 2/28/2009

If Cell A1 is 1/1/2009 then A2 should automatically calculate 12/1/2008 so
that years and leap years are accounted for.

Ultimately I want to be able to calculate the same month in the previous
year but I think I can do this with Month(A1)&"/"&Day(A1)&"/"&Year(A1)-1.

I was trying to find a DateAdd function but can't so I am not sure how to
approach this directly in Excel or should I get into VBA (never really done
this before).

Any help is appreciated TIA rasinc

Excel 2000 and date math
 

Try this:

=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))

Note that if you put 2/28/2008 in A1, you will get 3/1/2007 as the
result (i.e. 1st March, as 29th Feb 2007 doesn't exist) - is this what
you want?

Hope this helps.

Pete

On Jul 15, 9:34*pm, rasinc wrote:
I am trying to allow a user to enter a date and then calculate the month
before.

Eg. Enter in Cell A1 3/31/2009
Cell A2 will calculate automatically 2/28/2009

If Cell A1 is 1/1/2009 then A2 should automatically calculate 12/1/2008 so
that years and leap years are accounted for.

Ultimately I want to be able to calculate the same month in the previous
year but I think I can do this with Month(A1)&"/"&Day(A1)&"/"&Year(A1)-1.

I was trying to find a DateAdd function but can't so I am not sure how to
approach this directly in Excel or should I get into VBA (never really done
this before).

Any help is appreciated TIA rasinc

Excel 2000 and date math
 

=DATE(YEAR(A1),MONTH(A1)-1,MIN(DAY(DATE(YEAR(A1),MONTH(A1),0)),DAY(A1)))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"rasinc" wrote in message
...
I am trying to allow a user to enter a date and then calculate the month
before.

Eg. Enter in Cell A1 3/31/2009
Cell A2 will calculate automatically 2/28/2009

If Cell A1 is 1/1/2009 then A2 should automatically calculate 12/1/2008 so
that years and leap years are accounted for.

Ultimately I want to be able to calculate the same month in the previous
year but I think I can do this with Month(A1)&"/"&Day(A1)&"/"&Year(A1)-1.

I was trying to find a DateAdd function but can't so I am not sure how to
approach this directly in Excel or should I get into VBA (never really
done
this before).

Any help is appreciated TIA rasinc

Excel 2000 and date math
 

=DATE(Year(A1),Month(A1)-1,Day(A1)) will give 3/3/09 in the case of your
first example. You may need to derive your own function if that's not what
you want.
Perhaps =MIN(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)),DATE(YEAR(A1),MONTH(A1),0))
?

=DATE(Year(A1)-1,Month(A1),Day(A1)) will give a year earlier, but to cope
with an input such as 29 Feb 2008 you may want
=MIN(DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)),DATE(YEAR(A1)-1,MONTH(A1)+1,0)).
--
David Biddulph

rasinc wrote:
I am trying to allow a user to enter a date and then calculate the
month before.

Eg. Enter in Cell A1 3/31/2009
Cell A2 will calculate automatically 2/28/2009

If Cell A1 is 1/1/2009 then A2 should automatically calculate
12/1/2008 so that years and leap years are accounted for.

Ultimately I want to be able to calculate the same month in the
previous year but I think I can do this with
Month(A1)&"/"&Day(A1)&"/"&Year(A1)-1.

I was trying to find a DateAdd function but can't so I am not sure
how to approach this directly in Excel or should I get into VBA
(never really done this before).

Any help is appreciated TIA rasinc

Excel 2000 and date math
 

Hi rasinc,

For a date in A1:
=MIN(DATE(YEAR(A1),MONTH(A1)-1+{0,1},DAY(A1)*{1,0}))
You can use the above for any number of months before & after the nominated date, simply by changing the '-1' to whatever you need.

--
Cheers
macropod
[Microsoft MVP - Word]


"rasinc" wrote in message ...
I am trying to allow a user to enter a date and then calculate the month
before.

Eg. Enter in Cell A1 3/31/2009
Cell A2 will calculate automatically 2/28/2009

If Cell A1 is 1/1/2009 then A2 should automatically calculate 12/1/2008 so
that years and leap years are accounted for.

Ultimately I want to be able to calculate the same month in the previous
year but I think I can do this with Month(A1)&"/"&Day(A1)&"/"&Year(A1)-1.

I was trying to find a DateAdd function but can't so I am not sure how to
approach this directly in Excel or should I get into VBA (never really done
this before).

Any help is appreciated TIA rasinc