Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What date is7 days from given date in Excel 2000 | Excel Discussion (Misc queries) | |||
Excel 2000 - Highlighting a date. | Excel Discussion (Misc queries) | |||
Date Math | Excel Discussion (Misc queries) | |||
Date Math Problem | Excel Worksheet Functions | |||
In Excel, I need a date math formula... | Excel Discussion (Misc queries) |