Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding months to date at end of month
Hi,
I have searched but can't see this specific question. Basically the question is how to add moths to a date, i need to have a column that has column F's dates + 10 months. However, the column F dates are all month end days, so 31/8/07 or 28/02/06 or 30/06/07. I have tried the edate function and also tried date(year(f),month(f)+10, day(f)) formula, but both gives 28/12/07 if the figure in the original column was February. Is there a way of rounding the date up or down to the last day of the month? Many thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding months to date at end of month
"Shaggyjh" wrote in message
... Hi, I have searched but can't see this specific question. Basically the question is how to add moths to a date, i need to have a column that has column F's dates + 10 months. However, the column F dates are all month end days, so 31/8/07 or 28/02/06 or 30/06/07. I have tried the edate function and also tried date(year(f),month(f)+10, day(f)) formula, but both gives 28/12/07 if the figure in the original column was February. Is there a way of rounding the date up or down to the last day of the month? Many thanks Try this: =DATE(YEAR(A1),MONTH(A1)+11,0) It adds 11 months to your date, but then gives the "0th" of that month, that is the day before the 1st, or the last day of the previous month. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding months to date at end of month
Unless I've completely misunderstood your requirement, try this:-
=DATE(YEAR(F1),MONTH(F1)+10,DAY(F1)) Mike "Shaggyjh" wrote: Hi, I have searched but can't see this specific question. Basically the question is how to add moths to a date, i need to have a column that has column F's dates + 10 months. However, the column F dates are all month end days, so 31/8/07 or 28/02/06 or 30/06/07. I have tried the edate function and also tried date(year(f),month(f)+10, day(f)) formula, but both gives 28/12/07 if the figure in the original column was February. Is there a way of rounding the date up or down to the last day of the month? Many thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding months to date at end of month
Yes I may well have misunderstood, try this instead
=EOMONTH(F1,10) Mike "Shaggyjh" wrote: Hi, I have searched but can't see this specific question. Basically the question is how to add moths to a date, i need to have a column that has column F's dates + 10 months. However, the column F dates are all month end days, so 31/8/07 or 28/02/06 or 30/06/07. I have tried the edate function and also tried date(year(f),month(f)+10, day(f)) formula, but both gives 28/12/07 if the figure in the original column was February. Is there a way of rounding the date up or down to the last day of the month? Many thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding months to date at end of month
Thanks alot Stephen that works a treat.
"Stephen" wrote: "Shaggyjh" wrote in message ... Hi, I have searched but can't see this specific question. Basically the question is how to add moths to a date, i need to have a column that has column F's dates + 10 months. However, the column F dates are all month end days, so 31/8/07 or 28/02/06 or 30/06/07. I have tried the edate function and also tried date(year(f),month(f)+10, day(f)) formula, but both gives 28/12/07 if the figure in the original column was February. Is there a way of rounding the date up or down to the last day of the month? Many thanks Try this: =DATE(YEAR(A1),MONTH(A1)+11,0) It adds 11 months to your date, but then gives the "0th" of that month, that is the day before the 1st, or the last day of the previous month. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding months to a date | Excel Worksheet Functions | |||
Adding 6 Months to a Date | Excel Worksheet Functions | |||
Count months away betwwen a strat and end date by month | Excel Worksheet Functions | |||
Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
Adding 6 months to any given date | New Users to Excel |