Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula
I want to add 12 months to a start date (inclusive of the start date) and
return the end date. Start date will only be the first or last day of the month. Calculated end date must only return the first or last day of the month. Eg: Start 06/30/2009 Add 12 months Result should = 05/31/2010 Eg: Start 06/01/2009 Add 12 months Result should = 05/01/2009 Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula
Why does adding 12 months (1 year) to the beginning or end of June take you
to the month of May? If that is correct, then this formula should do what you want... =A1-DAY(A1)-(DAY(A1)=1)*((DAY(A1-DAY(A1))-1)) If you will copy this formula down for cells in Column A that are blank, then use this variation to suppress the zero-date that would be returned for blank cells... =IF(A3="","",A3-DAY(A3)-(DAY(A3)=1)*((DAY(A3-DAY(A3))-1))) -- Rick (MVP - Excel) "nibbana" wrote in message ... I want to add 12 months to a start date (inclusive of the start date) and return the end date. Start date will only be the first or last day of the month. Calculated end date must only return the first or last day of the month. Eg: Start 06/30/2009 Add 12 months Result should = 05/31/2010 Eg: Start 06/01/2009 Add 12 months Result should = 05/01/2009 Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula
That's right Rick. I want 12 (or other variable number) billing periods that
include the start date as one of them. You're formula result goes back 1 year instead of forward. Can you post through an amendment? Here's my actual test: A1: 30-Jun-06 A2: 12 (or any other variable periods) A3: 31-May-07 (your formula goes here) Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula
Oh, you want a variable time period (you didn't say that initially)... then
the approach I tried to use in my previous response cannot be patched to handle that. Try this instead. First, make sure you have the Analysis ToolPak Add-In selected (Tools/Add-Ins from the menu bar) and then put this formula in A3... =IF(A1="","",EOMONTH(A1,A2-1-(DAY(A1)=1))+(DAY(A1)=1)) -- Rick (MVP - Excel) "nibbana" wrote in message ... That's right Rick. I want 12 (or other variable number) billing periods that include the start date as one of them. You're formula result goes back 1 year instead of forward. Can you post through an amendment? Here's my actual test: A1: 30-Jun-06 A2: 12 (or any other variable periods) A3: 31-May-07 (your formula goes here) Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula
Mate, you're a legend. Well done that works a treat.
|
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) |