Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date
Hello All
New to this, so this question may have been answered before - don't know. I want to be able to add one calendar month and then 2 days to any date. Any ideas? Thanks R |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date
Try this:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+2) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "robzrob" wrote in message ups.com... Hello All New to this, so this question may have been answered before - don't know. I want to be able to add one calendar month and then 2 days to any date. Any ideas? Thanks R |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+2)
[subject to the usual question as to what you mean by adding a calendar month to a date like 31st January]. -- David Biddulph "robzrob" wrote in message ups.com... Hello All New to this, so this question may have been answered before - don't know. I want to be able to add one calendar month and then 2 days to any date. Any ideas? Thanks R |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date
On Oct 7, 5:58 pm, robzrob wrote:
Hello All New to this, so this question may have been answered before - don't know. I want to be able to add one calendar month and then 2 days to any date. Any ideas? Thanks R Thanks all. This raises another question now! I was assuming that adding a calendar month meant any of, for example: 28/01/07, 29/01/07, 30/01/07, 31/01/07 would become 28/02/07 and, for example: 31/08/07 would become 30/09/07, etc, in which case your formula won't work. So does adding a calendar month mean adding the number of days in the month the date in question is actually IN - in which case your formula WILL work? Hmm. (Sorry - the CAPS mean emphasis - not shouting - don't know how to italicise here.) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date
So if adding 1 month should never take you beyond the end of the next month try
=EDATE(A1,1)+2 EDATE is part of Analysis ToolPak add-in. To get the same result without the add-in try =MIN(DATE(YEAR(A1),MONTH(A1)+1+{1,0},DAY(A1)*{0,1} ))+2 "robzrob" wrote: On Oct 7, 5:58 pm, robzrob wrote: Hello All New to this, so this question may have been answered before - don't know. I want to be able to add one calendar month and then 2 days to any date. Any ideas? Thanks R Thanks all. This raises another question now! I was assuming that adding a calendar month meant any of, for example: 28/01/07, 29/01/07, 30/01/07, 31/01/07 would become 28/02/07 and, for example: 31/08/07 would become 30/09/07, etc, in which case your formula won't work. So does adding a calendar month mean adding the number of days in the month the date in question is actually IN - in which case your formula WILL work? Hmm. (Sorry - the CAPS mean emphasis - not shouting - don't know how to italicise here.) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date
On Oct 7, 7:40 pm, daddylonglegs wrote:
So if adding 1 month should never take you beyond the end of the next month try =EDATE(A1,1)+2 EDATE is part of Analysis ToolPak add-in. To get the same result without the add-in try =MIN(DATE(YEAR(A1),MONTH(A1)+1+{1,0},DAY(A1)*{0,1} ))+2 "robzrob" wrote: On Oct 7, 5:58 pm, robzrob wrote: Hello All New to this, so this question may have been answered before - don't know. I want to be able to add one calendar month and then 2 days to any date. Any ideas? Thanks R Thanks all. This raises another question now! I was assuming that adding a calendar month meant any of, for example: 28/01/07, 29/01/07, 30/01/07, 31/01/07 would become 28/02/07 and, for example: 31/08/07 would become 30/09/07, etc, in which case your formula won't work. So does adding a calendar month mean adding the number of days in the month the date in question is actually IN - in which case your formula WILL work? Hmm. (Sorry - the CAPS mean emphasis - not shouting - don't know how to italicise here.)- Hide quoted text - - Show quoted text - Thanks DLL - seems to be working - now I can relax! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Figuring Vacation Hrs. Earned using Current Date minus Hire Date | Excel Worksheet Functions | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel |