| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to add a date to a cell that is 6 months from today
Greetings,
I need to write a formula that is 6 months past any given date. I am not sure what the correct terminology is but in terms of being 6 months out, it needs to be 6 calendar months. Here is my current formula: =DATE(YEAR(A1), MONTH(A1)+6, DAY(A1)) When the day part of the date is 29, 30 or 31 _and_ 6 months from that date doesn't contain a day that is 29, 30 or 31 my formula rolls over to the following month. For example if my date in A1 is 8/29/2008 the result of my formula is 3/1/2009. What I really want is 2/28/2008, which is the last day of the 6th month from 3/1. What is the best way to special case dates where the day value is 29, 30 or 31. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to add a date to a cell that is 6 months from today
I'm sure someone is going to come up with something much simpler but until
then:- =IF(DAY(A1)=29,EOMONTH(A1,6),DATE(YEAR(A1), MONTH(A1)+6, DAY(A1))) With your date in a1 Mike "JEFFWI" wrote: Greetings, I need to write a formula that is 6 months past any given date. I am not sure what the correct terminology is but in terms of being 6 months out, it needs to be 6 calendar months. Here is my current formula: =DATE(YEAR(A1), MONTH(A1)+6, DAY(A1)) When the day part of the date is 29, 30 or 31 _and_ 6 months from that date doesn't contain a day that is 29, 30 or 31 my formula rolls over to the following month. For example if my date in A1 is 8/29/2008 the result of my formula is 3/1/2009. What I really want is 2/28/2008, which is the last day of the 6th month from 3/1. What is the best way to special case dates where the day value is 29, 30 or 31. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to add a date to a cell that is 6 months from today
Forgot to mention, if you get the VALUE error then
Tools|Addins and check the analysis toolpack Mike "JEFFWI" wrote: Greetings, I need to write a formula that is 6 months past any given date. I am not sure what the correct terminology is but in terms of being 6 months out, it needs to be 6 calendar months. Here is my current formula: =DATE(YEAR(A1), MONTH(A1)+6, DAY(A1)) When the day part of the date is 29, 30 or 31 _and_ 6 months from that date doesn't contain a day that is 29, 30 or 31 my formula rolls over to the following month. For example if my date in A1 is 8/29/2008 the result of my formula is 3/1/2009. What I really want is 2/28/2008, which is the last day of the 6th month from 3/1. What is the best way to special case dates where the day value is 29, 30 or 31. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to add a date to a cell that is 6 months from today
=DATE(YEAR(A1), MONTH(A1)+6,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1 )+7,0))))
Accounts for leap years too. Mind the 7 in the formula; if you change the number of future months , change this accordingly. -- Kind regards, Niek Otten Microsoft MVP - Excel "JEFFWI" wrote in message ... | Greetings, | | I need to write a formula that is 6 months past any given date. I am not | sure what the correct terminology is but in terms of being 6 months out, it | needs to be 6 calendar months. Here is my current formula: | | =DATE(YEAR(A1), MONTH(A1)+6, DAY(A1)) | | When the day part of the date is 29, 30 or 31 _and_ 6 months from that date | doesn't contain a day that is 29, 30 or 31 my formula rolls over to the | following month. For example if my date in A1 is 8/29/2008 the result of my | formula is 3/1/2009. What I really want is 2/28/2008, which is the last day | of the 6th month from 3/1. | | What is the best way to special case dates where the day value is 29, 30 or | 31. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to add a date to a cell that is 6 months from today
Thanks guys! Works great!
"Niek Otten" wrote: =DATE(YEAR(A1), MONTH(A1)+6,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1 )+7,0)))) Accounts for leap years too. Mind the 7 in the formula; if you change the number of future months , change this accordingly. -- Kind regards, Niek Otten Microsoft MVP - Excel "JEFFWI" wrote in message ... | Greetings, | | I need to write a formula that is 6 months past any given date. I am not | sure what the correct terminology is but in terms of being 6 months out, it | needs to be 6 calendar months. Here is my current formula: | | =DATE(YEAR(A1), MONTH(A1)+6, DAY(A1)) | | When the day part of the date is 29, 30 or 31 _and_ 6 months from that date | doesn't contain a day that is 29, 30 or 31 my formula rolls over to the | following month. For example if my date in A1 is 8/29/2008 the result of my | formula is 3/1/2009. What I really want is 2/28/2008, which is the last day | of the 6th month from 3/1. | | What is the best way to special case dates where the day value is 29, 30 or | 31. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to add a date to a cell that is 6 months from today
Hmmmmmm
=EDATE(A1,6) Mike "JEFFWI" wrote: Greetings, I need to write a formula that is 6 months past any given date. I am not sure what the correct terminology is but in terms of being 6 months out, it needs to be 6 calendar months. Here is my current formula: =DATE(YEAR(A1), MONTH(A1)+6, DAY(A1)) When the day part of the date is 29, 30 or 31 _and_ 6 months from that date doesn't contain a day that is 29, 30 or 31 my formula rolls over to the following month. For example if my date in A1 is 8/29/2008 the result of my formula is 3/1/2009. What I really want is 2/28/2008, which is the last day of the 6th month from 3/1. What is the best way to special case dates where the day value is 29, 30 or 31. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula to add a date to a cell that is 6 months from today
LOL!
-- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... | Hmmmmmm | | =EDATE(A1,6) | | Mike | | "JEFFWI" wrote: | | Greetings, | | I need to write a formula that is 6 months past any given date. I am not | sure what the correct terminology is but in terms of being 6 months out, it | needs to be 6 calendar months. Here is my current formula: | | =DATE(YEAR(A1), MONTH(A1)+6, DAY(A1)) | | When the day part of the date is 29, 30 or 31 _and_ 6 months from that date | doesn't contain a day that is 29, 30 or 31 my formula rolls over to the | following month. For example if my date in A1 is 8/29/2008 the result of my | formula is 3/1/2009. What I really want is 2/28/2008, which is the last day | of the 6th month from 3/1. | | What is the best way to special case dates where the day value is 29, 30 or | 31. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Keep 6 months of dates from Today to (Today + 6 Months) | Excel Programming | |||
| dynamic calculation of months remaining given end date and today | Excel Discussion (Misc queries) | |||
| IF TODAY equals date in cell A10, or if TODAY is beyond that date | Excel Worksheet Functions | |||
| Need the formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
| FORMULA TO INCREMENT 25 MONTHS BASED ON A DATE CELL | Excel Discussion (Misc queries) | |||
Hybrid Mode
