| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with creating a formula
Does anyone know how to create a formula that will calculate 3 & 30 months
from any given date? Date 3 mon 30 mon Example: 1/1/07----- 4/1/07----- 11/1/08 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Need help with creating a formula
Try =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))
you may need to format the cell to show a date if you see a number like 39173 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Kimberly" wrote in message ... Does anyone know how to create a formula that will calculate 3 & 30 months from any given date? Date 3 mon 30 mon Example: 1/1/07----- 4/1/07----- 11/1/08 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Need help with creating a formula
But keep in mind the short months such as Feb if it is your intention to
always have the day the same. For instance, if the start date is 11/30/06, the formula will produce 3/2/07 instead of 2/30/07 since there is no 2/30/07. -- Y "Bernard Liengme" wrote: Try =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) you may need to format the cell to show a date if you see a number like 39173 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Kimberly" wrote in message ... Does anyone know how to create a formula that will calculate 3 & 30 months from any given date? Date 3 mon 30 mon Example: 1/1/07----- 4/1/07----- 11/1/08 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Need help with creating a formula
Regarding your example, I don't think the result for 30 months is 11/1/08. It should be 7/1/2009.
Another way: In cell A1: 1/1/07 For 3 months: =EDATE(A1,3) For 30 months: =EDATE(A1,30) Like Bernard said format cell ...... If EDATE function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Epinn "Kimberly" wrote in message ... Does anyone know how to create a formula that will calculate 3 & 30 months from any given date? Date 3 mon 30 mon Example: 1/1/07----- 4/1/07----- 11/1/08 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Need help with creating a formula
Please note the nature of EDATE.
From Excel Help: Use EDATE to calculate maturity dates or due dates that fall on the **same** day of the month as the date of issue. Epinn "Epinn" wrote in message ... Regarding your example, I don't think the result for 30 months is 11/1/08. It should be 7/1/2009. Another way: In cell A1: 1/1/07 For 3 months: =EDATE(A1,3) For 30 months: =EDATE(A1,30) Like Bernard said format cell ...... If EDATE function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Epinn "Kimberly" wrote in message ... Does anyone know how to create a formula that will calculate 3 & 30 months from any given date? Date 3 mon 30 mon Example: 1/1/07----- 4/1/07----- 11/1/08 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Need help with creating a formula
Was not aware of the EDATE function. Thanks.
-- Y "Epinn" wrote: Please note the nature of EDATE. From Excel Help: Use EDATE to calculate maturity dates or due dates that fall on the **same** day of the month as the date of issue. Epinn "Epinn" wrote in message ... Regarding your example, I don't think the result for 30 months is 11/1/08. It should be 7/1/2009. Another way: In cell A1: 1/1/07 For 3 months: =EDATE(A1,3) For 30 months: =EDATE(A1,30) Like Bernard said format cell ...... If EDATE function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Epinn "Kimberly" wrote in message ... Does anyone know how to create a formula that will calculate 3 & 30 months from any given date? Date 3 mon 30 mon Example: 1/1/07----- 4/1/07----- 11/1/08 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Need help with creating a formula
Y, Thanks for your input.
Just verified that EDATE takes care of leap year February as well. I worry too much or I am detailed? <G A1: 12/31/2007 =EDATE(A1,2) returns 2/29/08 when cell is formatted as date. Kimberly, please note that Bernard's formula and mine will give different results. Not sure what you want exactly, but I think we have covered two scenarios. Is there a third one? Epinn "Yacbo" wrote in message ... Was not aware of the EDATE function. Thanks. -- Y "Epinn" wrote: Please note the nature of EDATE. From Excel Help: Use EDATE to calculate maturity dates or due dates that fall on the **same** day of the month as the date of issue. Epinn "Epinn" wrote in message ... Regarding your example, I don't think the result for 30 months is 11/1/08. It should be 7/1/2009. Another way: In cell A1: 1/1/07 For 3 months: =EDATE(A1,3) For 30 months: =EDATE(A1,30) Like Bernard said format cell ...... If EDATE function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Epinn "Kimberly" wrote in message ... Does anyone know how to create a formula that will calculate 3 & 30 months from any given date? Date 3 mon 30 mon Example: 1/1/07----- 4/1/07----- 11/1/08 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Need help with creating a formula
I think my concern about leap year and February is justified and I don't worry too much. Just found something interesting.
http://support.microsoft.com/kb/179583 More importantly, MS has fixed the problem. This problem no longer occurs in Microsoft Excel 2000. << EDATE may not be good for versions prior to 2000?? If I have missed something, please feel free to let us know. Epinn "Epinn" wrote in message ... Y, Thanks for your input. Just verified that EDATE takes care of leap year February as well. I worry too much or I am detailed? <G A1: 12/31/2007 =EDATE(A1,2) returns 2/29/08 when cell is formatted as date. Kimberly, please note that Bernard's formula and mine will give different results. Not sure what you want exactly, but I think we have covered two scenarios. Is there a third one? Epinn "Yacbo" wrote in message ... Was not aware of the EDATE function. Thanks. -- Y "Epinn" wrote: Please note the nature of EDATE. From Excel Help: Use EDATE to calculate maturity dates or due dates that fall on the **same** day of the month as the date of issue. Epinn "Epinn" wrote in message ... Regarding your example, I don't think the result for 30 months is 11/1/08. It should be 7/1/2009. Another way: In cell A1: 1/1/07 For 3 months: =EDATE(A1,3) For 30 months: =EDATE(A1,30) Like Bernard said format cell ...... If EDATE function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Epinn "Kimberly" wrote in message ... Does anyone know how to create a formula that will calculate 3 & 30 months from any given date? Date 3 mon 30 mon Example: 1/1/07----- 4/1/07----- 11/1/08 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a formula | Excel Worksheet Functions | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Creating a complicated formula | Excel Worksheet Functions | |||
| need help creating formula based on cell value | Excel Discussion (Misc queries) | |||
| Creating a specific formula | New Users to Excel | |||
Linear Mode
