Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel formula for "next month same day"
Hi Wizards,
I struggled a lot trying to find out formula returning "next month same day" but also considering "next month END" i.e. A1: 29-Feb-04, A2: 31-Mar-04, A3: 30-Apr-04. I found a way a month ago playing with the date functions (I think) but lost the file and am not able to reproduce the formula now. Anybody?.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel formula for "next month same day"
In Cell A1 put Jan 31. In Cell A2 add this formula...
=EOMONTH(A1+1, 0) You can drag this formula down and it will give you the last day of the following month. You need to install the Analysis Toolpack to use this formula if you have not already. Tools - Addins - Analysis Toolpack -- HTH... Jim Thomlinson "Ubi bene .." wrote: Hi Wizards, I struggled a lot trying to find out formula returning "next month same day" but also considering "next month END" i.e. A1: 29-Feb-04, A2: 31-Mar-04, A3: 30-Apr-04. I found a way a month ago playing with the date functions (I think) but lost the file and am not able to reproduce the formula now. Anybody?.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel formula for "next month same day"
Thx a lot, Jim!
I didn't know how powerfull this "toolpack" is. "Jim Thomlinson" wrote: In Cell A1 put Jan 31. In Cell A2 add this formula... =EOMONTH(A1+1, 0) You can drag this formula down and it will give you the last day of the following month. You need to install the Analysis Toolpack to use this formula if you have not already. Tools - Addins - Analysis Toolpack -- HTH... Jim Thomlinson "Ubi bene .." wrote: Hi Wizards, I struggled a lot trying to find out formula returning "next month same day" but also considering "next month END" i.e. A1: 29-Feb-04, A2: 31-Mar-04, A3: 30-Apr-04. I found a way a month ago playing with the date functions (I think) but lost the file and am not able to reproduce the formula now. Anybody?.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel formula for "next month same day"
On Wed, 19 Oct 2005 14:01:05 -0700, "Ubi bene .." <Ubi bene
wrote: Hi Wizards, I struggled a lot trying to find out formula returning "next month same day" but also considering "next month END" i.e. A1: 29-Feb-04, A2: 31-Mar-04, A3: 30-Apr-04. I found a way a month ago playing with the date functions (I think) but lost the file and am not able to reproduce the formula now. Anybody?.. If you have the ATP installed, and your first date is in A1, then =EDATE($A$1,ROW()-CELL("row",$A$1)) This will give you the same date of the month each month, but not run over the end of the month. Note that in order to do this, you need to refer back to the first cell. Drag the formula down as far as needed. If your formula is in a single row, then change "row" to "col" in the formula. --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel formula for "next month same day"
Here's a very quick non-formula way to get the same date each month.
Let's say you want to show the 20th of each month starting with 10/20/2005 beginning in cell A1. 1.) Enter 10/20/2005 in cell A1 and enter 11/20/2005 in cell A2. 2.) Highlight both cells. 3.) Drag the fill handle (the tiny black square in the bottom right corner of the selected cells) down the desired number of rows and each cell will now show the 20th of each month. As with the formulas given, this will not work correctly if you use a starting date that exceeds a valid date in other months (e.g., if you start with 3/31/2005, it will not work because there is no 4/31/2005.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
Function or formula to convert "text" month to number of month? | Excel Discussion (Misc queries) | |||
Function or formula to convert "text" month to number of month? | Excel Discussion (Misc queries) | |||
Function or formula to convert "text" month to number of month | Excel Discussion (Misc queries) |