| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I select same day of month, 3 mths apart when day 31 of mth
I want a table of dates for every 3 months after a starting date (could be
15th, 30th 31st etc). Difficulty when it is for example March 31 . The formula : =DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date)) provides July 1. The correct date should be June 30. The above formula works fine with days in the mid-month eg. 15th I am wanting to avoid using the EOMONTH due to some starting dates are mid-month. Formula should also deal with February! Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I select same day of month, 3 mths apart when day 31 of mth
Please try this one out, with the start date in A1. Does it
accommodate mid-month start dates properly? =EOMONTH(A1,3)-(DAY(EOMONTH(A1,0))-DAY(A1)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I select same day of month, 3 mths apart when day 31 of mth
Maybe:
=DATE(YEAR(A1),MONTH(A1)+3,MIN(DAY(A1),DAY(EOMONTH (A1,3)))) "JohnNZ" wrote: I want a table of dates for every 3 months after a starting date (could be 15th, 30th 31st etc). Difficulty when it is for example March 31 . The formula : =DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date)) provides July 1. The correct date should be June 30. The above formula works fine with days in the mid-month eg. 15th I am wanting to avoid using the EOMONTH due to some starting dates are mid-month. Formula should also deal with February! Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I select same day of month, 3 mths apart when day 31 of
Brilliant - thanks Dave O!!
"Dave O" wrote: Please try this one out, with the start date in A1. Does it accommodate mid-month start dates properly? =EOMONTH(A1,3)-(DAY(EOMONTH(A1,0))-DAY(A1)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I select same day of month, 3 mths apart when day 31 of
Thanks, but seemed to not be consistent with day of month later on in the
year eg. after 6, or 9 months. "JMB" wrote: Maybe: =DATE(YEAR(A1),MONTH(A1)+3,MIN(DAY(A1),DAY(EOMONTH (A1,3)))) "JohnNZ" wrote: I want a table of dates for every 3 months after a starting date (could be 15th, 30th 31st etc). Difficulty when it is for example March 31 . The formula : =DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date)) provides July 1. The correct date should be June 30. The above formula works fine with days in the mid-month eg. 15th I am wanting to avoid using the EOMONTH due to some starting dates are mid-month. Formula should also deal with February! Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I select same day of month, 3 mths apart when day 31 of
Which date(s) tripped up the formula?
"JohnNZ" wrote: Thanks, but seemed to not be consistent with day of month later on in the year eg. after 6, or 9 months. "JMB" wrote: Maybe: =DATE(YEAR(A1),MONTH(A1)+3,MIN(DAY(A1),DAY(EOMONTH (A1,3)))) "JohnNZ" wrote: I want a table of dates for every 3 months after a starting date (could be 15th, 30th 31st etc). Difficulty when it is for example March 31 . The formula : =DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date)) provides July 1. The correct date should be June 30. The above formula works fine with days in the mid-month eg. 15th I am wanting to avoid using the EOMONTH due to some starting dates are mid-month. Formula should also deal with February! Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I select same day of month, 3 mths apart when day 31 of
Sorry JMB this was my first time using on-line community, and I got my
replies mixed. Your formula was perfect. Thanks again. "JMB" wrote: Which date(s) tripped up the formula? "JohnNZ" wrote: Thanks, but seemed to not be consistent with day of month later on in the year eg. after 6, or 9 months. "JMB" wrote: Maybe: =DATE(YEAR(A1),MONTH(A1)+3,MIN(DAY(A1),DAY(EOMONTH (A1,3)))) "JohnNZ" wrote: I want a table of dates for every 3 months after a starting date (could be 15th, 30th 31st etc). Difficulty when it is for example March 31 . The formula : =DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date)) provides July 1. The correct date should be June 30. The above formula works fine with days in the mid-month eg. 15th I am wanting to avoid using the EOMONTH due to some starting dates are mid-month. Formula should also deal with February! Thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I select same day of month, 3 mths apart when day 31 of mth
On Wed, 24 May 2006 18:48:01 -0700, JohnNZ
wrote: I want a table of dates for every 3 months after a starting date (could be 15th, 30th 31st etc). Difficulty when it is for example March 31 . The formula : =DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date)) provides July 1. The correct date should be June 30. The above formula works fine with days in the mid-month eg. 15th I am wanting to avoid using the EOMONTH due to some starting dates are mid-month. Formula should also deal with February! Thanks You can use the EDATE function. You should also be sure to always reference back to the starting date, otherwise three months after 9/30 will be 12/30 when you probably want 12/31. One method of referencing back to the starting date, with the starting date in A1 and your table in column A: A2: =edate($A$1,ROWS($1:1)*3) and copy/drag down. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA | Excel Worksheet Functions | |||
| Calculate Days in a Month | Excel Discussion (Misc queries) | |||
| Help!!! Vlookup!! | Excel Worksheet Functions | |||
| Cells User Select Locked after upgrade to Excel 2002 | Excel Discussion (Misc queries) | |||
| how do you "select locked cells" w/o "select unlocked cells"? | Excel Discussion (Misc queries) | |||
Linear Mode
