Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Excel to calculate future dates with various intervals
Hi,
I need to build a calendar, where I have a given start date. From this start date, I need to capture future predicted dates, based on various intervals. How do I do this? Example: Start Date: 1/1/09 Future Date intervals: 1 week, 2 weeks, 1 month, 3 months, 12 months, 18 months, 2 years Thanks for your help with this. MH |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Excel to calculate future dates with various intervals
Let's assume your start date is 2/29/2008 and you want the future date in 12
months (1 year). What result do you expext? Start date is 1/31/2009 and you want the future date in 1 month. What result do you expect? The future dates for the weeks is pretty straightforward: 1 week: =start_date+7 2 weeks: =start_date+14 For the months and years, well, you have to think about those for a minute and decide what the correct end date should be because months and years don't have the same number of days in them! -- Biff Microsoft Excel MVP "MH" wrote in message ... Hi, I need to build a calendar, where I have a given start date. From this start date, I need to capture future predicted dates, based on various intervals. How do I do this? Example: Start Date: 1/1/09 Future Date intervals: 1 week, 2 weeks, 1 month, 3 months, 12 months, 18 months, 2 years Thanks for your help with this. MH |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Excel to calculate future dates with various intervals
Hi T. Valko,
For your examples, I would want it to end 1 calendar month or year later, regardless of the odd number of days (i.e. 2/29/09 would end one month later 2/29/09 or one year later 2/29/10. If this exceeds the number of days in the month it would fall on, then I would need it to fall on the last day of that month, say 2/28/09. What is the proper method to calculate these days if this is the case? Thanks. "T. Valko" wrote: Let's assume your start date is 2/29/2008 and you want the future date in 12 months (1 year). What result do you expext? Start date is 1/31/2009 and you want the future date in 1 month. What result do you expect? The future dates for the weeks is pretty straightforward: 1 week: =start_date+7 2 weeks: =start_date+14 For the months and years, well, you have to think about those for a minute and decide what the correct end date should be because months and years don't have the same number of days in them! -- Biff Microsoft Excel MVP "MH" wrote in message ... Hi, I need to build a calendar, where I have a given start date. From this start date, I need to capture future predicted dates, based on various intervals. How do I do this? Example: Start Date: 1/1/09 Future Date intervals: 1 week, 2 weeks, 1 month, 3 months, 12 months, 18 months, 2 years Thanks for your help with this. MH |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Excel to calculate future dates with various intervals
Ok....
For months or years: =EDATE(start_date,n) Where n = number of months. Express years in months: 1 year =12 months, 3 years = 36 months. The EDATE function requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. If you enter the formula and get a #NAME? error look in Excel help for the EDATE function and it'll tell you how to install the Analysis ToolPak add-in. -- Biff Microsoft Excel MVP "MH" wrote in message ... Hi T. Valko, For your examples, I would want it to end 1 calendar month or year later, regardless of the odd number of days (i.e. 2/29/09 would end one month later 2/29/09 or one year later 2/29/10. If this exceeds the number of days in the month it would fall on, then I would need it to fall on the last day of that month, say 2/28/09. What is the proper method to calculate these days if this is the case? Thanks. "T. Valko" wrote: Let's assume your start date is 2/29/2008 and you want the future date in 12 months (1 year). What result do you expext? Start date is 1/31/2009 and you want the future date in 1 month. What result do you expect? The future dates for the weeks is pretty straightforward: 1 week: =start_date+7 2 weeks: =start_date+14 For the months and years, well, you have to think about those for a minute and decide what the correct end date should be because months and years don't have the same number of days in them! -- Biff Microsoft Excel MVP "MH" wrote in message ... Hi, I need to build a calendar, where I have a given start date. From this start date, I need to capture future predicted dates, based on various intervals. How do I do this? Example: Start Date: 1/1/09 Future Date intervals: 1 week, 2 weeks, 1 month, 3 months, 12 months, 18 months, 2 years Thanks for your help with this. MH |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Excel to calculate future dates with various intervals
Hi,
You could also try the following 1. Suppose 1/1/2009 is entered in cell B4; 2. In B7:B13, enter 1,2,1,3,12,18,2 3. In C7:C13, enter weeks,weeks,months,months,months,months,years 4. In E7, enter the following formula and copy down =IF($C7="weeks",DATE(YEAR($B$4),MONTH($B$4),DAY($B $4)+($B7*7)),IF($C7="months",DATE(YEAR($B$4),MONTH ($B$4)+B7,DAY($B$4)),DATE(YEAR($B$4)+B7,MONTH($B$4 ),DAY($B$4)))) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "MH" wrote in message ... Hi, I need to build a calendar, where I have a given start date. From this start date, I need to capture future predicted dates, based on various intervals. How do I do this? Example: Start Date: 1/1/09 Future Date intervals: 1 week, 2 weeks, 1 month, 3 months, 12 months, 18 months, 2 years Thanks for your help with this. MH |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Excel to calculate future dates with various intervals
Thank you T. Valko and Ashish Mathur for your suggestions. I will certainly
give them a try. Do these formulas work in other formulas if I need to calculate number of days, months, or years etc with the same conditions applied? I imagine I would bracket the formula you gave me in the formula of interest. I don't have an example to apply at this time. Second question, slightly off topic: I will need to apply this date formula for various start dates and variable, in a single excel spreadsheet. Is there a way I can set this up so that it can autopopulate a series of cells with new dates, that were previously blank (ie. fill down the existing list), when given a product, ref doc., lot, and base date only. The first two items determine the intervals assigned, and the lot and base date would be the variables that would be added later that need the predicted dates autopopulated. Ex: A B C D E Product 1 Ref Doc Lot 1 Base Date Predicted Intervals (I would need it to populate downward or in a fashion that can be sorted based on the predicted intervals) Thanks very much for your help. MH "MH" wrote: Hi, I need to build a calendar, where I have a given start date. From this start date, I need to capture future predicted dates, based on various intervals. How do I do this? Example: Start Date: 1/1/09 Future Date intervals: 1 week, 2 weeks, 1 month, 3 months, 12 months, 18 months, 2 years Thanks for your help with this. MH |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Excel to calculate future dates with various intervals
Hi,
And for another idea 1. Enter 1/1/2009 in A1 2. Drag the fill handle down with the right mouse button when you release the mouse you will see choices for Fill Months Fill Years (12 months) 3. For weeks enter 1/1/2009 in A1 and 1/8/09 in A2, highlight both and drag the fill handle down (no right mouse this time) 4. For 2 week increments enter 1/1/2009 in A1 and 1/15/09 in A2, highlight both and fill down. 5. For 3 month increments enter 1/1/2009 in A1 and 4/1/2009 in A2 and repeat as above. 6. For 18 months enter 1/1/2009 and 6/1/2010 and repeat as above. 7. For 2 years use 1/1/2009 and 1/1/2011 -- If this helps, please click the Yes button Cheers, Shane Devenshire "MH" wrote: Hi, I need to build a calendar, where I have a given start date. From this start date, I need to capture future predicted dates, based on various intervals. How do I do this? Example: Start Date: 1/1/09 Future Date intervals: 1 week, 2 weeks, 1 month, 3 months, 12 months, 18 months, 2 years Thanks for your help with this. MH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Formulas In Excel To Calculate Time Intervals | Excel Worksheet Functions | |||
How do I calculate a future date in excel? | Excel Worksheet Functions | |||
How do I calculate a future date in Excel? | Excel Worksheet Functions | |||
How do I Calculate a future or past date in Excel? | Excel Discussion (Misc queries) | |||
Charting data against dates where dates are not at fixed intervals | Charts and Charting in Excel |