Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates
I have a cell in a spreadsheet with Today() in it which
returns the current system date. Is there a way of adding a month on to this. I know about the DateAdd function in VBA, but would rather use a cell formula if possible. TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates
one way
=DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)) -- Don Guillett SalesAid Software "LiSa" wrote in message ... I have a cell in a spreadsheet with Today() in it which returns the current system date. Is there a way of adding a month on to this. I know about the DateAdd function in VBA, but would rather use a cell formula if possible. TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates
LiSa
=DATE(YEAR(A1),MONTH(A1)+1,1) Gord Dibben Excel MVP On Sat, 12 Jun 2004 11:51:40 -0700, "LiSa" wrote: I have a cell in a spreadsheet with Today() in it which returns the current system date. Is there a way of adding a month on to this. I know about the DateAdd function in VBA, but would rather use a cell formula if possible. TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates
Gord forgot to include the day.
=DATE(YEAR(A1),MONTH(A1)+1,1) =DATE(YEAR(A1),MONTH(A1)+1,day(a1)) -- Don Guillett SalesAid Software "Gord Dibben" <gorddibbATshawDOTca wrote in message ... LiSa =DATE(YEAR(A1),MONTH(A1)+1,1) Gord Dibben Excel MVP On Sat, 12 Jun 2004 11:51:40 -0700, "LiSa" wrote: I have a cell in a spreadsheet with Today() in it which returns the current system date. Is there a way of adding a month on to this. I know about the DateAdd function in VBA, but would rather use a cell formula if possible. TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates
It all depends on how you define a "month". For example, the formula
suggested will yield 1-Oct-2004 if you add a month to 31-Aug-2004. The formula is correct; it gives inconsistent results solely due to the peculiarities of our calendar in which months can have anywhere from 28 to 31 days. -- Vasant "LiSa" wrote in message ... I have a cell in a spreadsheet with Today() in it which returns the current system date. Is there a way of adding a month on to this. I know about the DateAdd function in VBA, but would rather use a cell formula if possible. TIA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates
Thanks that works great, but I still have to reference the
cell D1 with =Today() in it. Is there any way I can have it all in one cell? -----Original Message----- one way =DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)) -- Don Guillett SalesAid Software "LiSa" wrote in message ... I have a cell in a spreadsheet with Today() in it which returns the current system date. Is there a way of adding a month on to this. I know about the DateAdd function in VBA, but would rather use a cell formula if possible. TIA . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates
Well, that's what you said you wanted. if you want it all in one cell
=DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)) =DATE(YEAR(today()),MONTH(today())+1,DAY(today())) -- Don Guillett SalesAid Software "LiSa" wrote in message ... Thanks that works great, but I still have to reference the cell D1 with =Today() in it. Is there any way I can have it all in one cell? -----Original Message----- one way =DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)) -- Don Guillett SalesAid Software "LiSa" wrote in message ... I have a cell in a spreadsheet with Today() in it which returns the current system date. Is there a way of adding a month on to this. I know about the DateAdd function in VBA, but would rather use a cell formula if possible. TIA . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates
Hi Lisa!
If the Day of Month is likely to be equal to or greater than 29, you need to make sure what you want. Take: A1: 31-Jan-2004 =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) Returns: 2-Mar-2004 And: A1: 31-Jan-2005 =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) Returns: 1-Mar-2005 If you prefer the last day of the month in cases where the day does not exist, use the formula: =DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY($A$1),DAY(DATE( YEAR(A1),MONTH(A1)+2,0)))) This is based upon a general formula that can be copied down or across of: =DATE(YEAR(A1),MONTH(A1)+AddMons,MIN(DAY($A$1),DAY (DATE(YEAR(A1),MONTH(A1)+AddMons+1,0)))) Where AddMons is the number of months to be added. And here’s an alternative that does the same by Peter Dorigo: =MIN(DATE(YEAR(A1),MONTH(A1)+ AddMons +{1,0},DAY($A$1)*{0,1})) For one off situations not involving copying down or accross, if you can rely upon Analysis ToolPak being installed and selected, you can use: =EDATE(A1,1) -- Regards Norman Harker MVP (Excel) Sydney, Australia "LiSa" wrote in message ... I have a cell in a spreadsheet with Today() in it which returns the current system date. Is there a way of adding a month on to this. I know about the DateAdd function in VBA, but would rather use a cell formula if possible. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking computer dates (time) to spreadsheet dates that have formu | Excel Worksheet Functions | |||
Stop dates from showing as numbers - when formated as dates | Excel Discussion (Misc queries) | |||
compare 2 tables of dates to find the preceding dates | Excel Worksheet Functions | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) |