Date Formula

  • Thread starter Thread starter Davey Boy
  • Start date Start date
D

Davey Boy

I need help with a formula that goes form cell to cell returning the next
month's date...

For example, in cell A10, I have current date "3/12/05" ... in cell A11,
I'd like to input a formula that will return "4/12/05" and so forth... What
formula can I use? ...

TIA!!!

LavaDude
 
Davey said:
I need help with a formula that goes form cell to cell returning the next
month's date...

For example, in cell A10, I have current date "3/12/05" ... in cell A11,
I'd like to input a formula that will return "4/12/05" and so forth... What
formula can I use? ...

TIA!!!

LavaDude
Click to expand...

It's confusing whether that date is in the format "mm-dd-yy" or
"dd-mm-yy" i.e 12-Mar or 3-Dec

To get the same date in the next month e.g.
3-Dec, 3-Jan, 3-Feb or 12-Mar, 12-Apr, 12-May try

=DATE(YEAR(A10),MONTH(A10)+1,DAY(A10))

This will "wrap round", when the month becomes 13 the Date formula makes
it Jan of the next year.
 
Your solution presumes that he is using a dd/mm/yy date convention.
In the U.S., mm/dd/yy is more common, where a couple of solutions would be
=A10+31-DAY(A10+31)+DAY(A10)
=EOMONTH(A10,0)+DAY(A10)
Either is fine for days not too late in the month. Neither may be
desirable for a date at the end of the month that may not exist in some
months (e.g. >=29Jan)

Jerry
 
I need help with a formula that goes form cell to cell returning the next
month's date...

For example, in cell A10, I have current date "3/12/05" ... in cell A11,
I'd like to input a formula that will return "4/12/05" and so forth... What
formula can I use? ...

TIA!!!

LavaDude
Click to expand...


=EDATE(A10,1)

However, any of the formulas can cause a problem if you are at the end of the
month. For example, if A10:= 31 Jan 2005, and you have the above formula in
A11 and drag it down, the results will be:

31-Jan
28-Feb
28-Mar
28-Apr
28-May
28-Jun
28-Jul
28-Aug
28-Sep

Where what you probably would want would be:

31-Jan
28-Feb
31-Mar
30-Apr
31-May
30-Jun
31-Jul
31-Aug
30-Sep


To get the latter, use a formula that always references back to your initial
date, such as:

A11: =edate($A$10,ROW()-10)


============================
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
 
It's confusing whether that date is in the format "mm-dd-yy" or
"dd-mm-yy" i.e 12-Mar or 3-Dec

To get the same date in the next month e.g.
3-Dec, 3-Jan, 3-Feb or 12-Mar, 12-Apr, 12-May try

=DATE(YEAR(A10),MONTH(A10)+1,DAY(A10))

This will "wrap round", when the month becomes 13 the Date formula
makes it Jan of the next year.
Click to expand...

This is what I was looking for...

Thanks! LavaDude (aka Davey Boy)
 
You don't need a formula if all you are doing is incrementing by 1 day as
you go down the column. All you need to do is place the two dates into A10
and A11 select the range A10:A11 and drag the fill handle down to fill as
many cells as you need.

If that is not what you want then in cell A11 enter the formula =A10+1.

Regards
Rob
 
Back
Top