Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE FORMULA
hello you clever peops - one day I may get the hang of this but today is not
the day. my spr is set out as so: contrct no start date no of months end date 301856 21/11/07 3 what i need to do is creat a formula that calclulates the end date - so essentially its the start date x 3 months can anyone help me? -- Kind regards Megan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE FORMULA
=DATE(YEAR(B2),MONTH(B2)+3,DAY(B2))
assuming the date is in B2, also assuming you meant +3 months. :) "Megan" wrote: hello you clever peops - one day I may get the hang of this but today is not the day. my spr is set out as so: contrct no start date no of months end date 301856 21/11/07 3 what i need to do is creat a formula that calclulates the end date - so essentially its the start date x 3 months can anyone help me? -- Kind regards Megan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE FORMULA
The following formula will work for you and the example assumes that the starting date is in cell B2: =DATE(YEAR(B2),MONTH(B2)+3,DAY(B2)) -- Kevin Backmann "Megan" wrote: hello you clever peops - one day I may get the hang of this but today is not the day. my spr is set out as so: contrct no start date no of months end date 301856 21/11/07 3 what i need to do is creat a formula that calclulates the end date - so essentially its the start date x 3 months can anyone help me? -- Kind regards Megan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE FORMULA
"Megan" wrote in message
... hello you clever peops - one day I may get the hang of this but today is not the day. my spr is set out as so: contrct no start date no of months end date 301856 21/11/07 3 what i need to do is creat a formula that calclulates the end date - so essentially its the start date x 3 months can anyone help me? -- Kind regards Megan I assume you mean that the end date is the start date + 3 months. For a start date in B2 and a "number of months" in C2, use this formula in D2: =DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE FORMULA
On Tue, 27 Nov 2007 06:11:06 -0800, Megan
wrote: hello you clever peops - one day I may get the hang of this but today is not the day. my spr is set out as so: contrct no start date no of months end date 301856 21/11/07 3 what i need to do is creat a formula that calclulates the end date - so essentially its the start date x 3 months can anyone help me? The previous formulas will work depending on what you want to happen when StartDate is at the end of a month. For example, given: StartDate 31 Dec 2007 and no of months = 2 The previous formulas will give a result of 2 Mar 2008. If that is OK, well and good. However, if you would prefer to not "run over" the end of a month, then the following formula will do that. With the above data, it will give a result of 29 Feb 2008 =MIN(DATE(YEAR(StartDate),MONTH(StartDate)+NumMont hs+{1,0},DAY(StartDate)*{0,1})) Also, if you have the Analysis Tool Pak installed, or if you have Excel 2007 which includes the ATP functions, you can use this formula: =EDATE(StartDate,NumMonths) If the EDATE function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) |