Adding Month(s) To a Date

beadedbytes · Jun 13, 2006

Using the information in 'MosLo'/'MosHi' columns below, is there a formula that will accurately return the dates listed under 'Date Lo' and 'Date Hi' columns? The date used for the calculations can be found in 'cell' A1.

On a different note, is there a way to copy/paste the example below into the tek-tip message box so that the columns line up properly?

Thank you in advance.

Example ColA ColB ColC ColD
row1 6/19/2006
row2
row3 Month Lo Month Hi Date Lo Date Hi
row4 0 6 6/19/2006 12/19/2005
row5 7 12 11/18/2005 6/19/2004
row6 13 24 11/18/2004 6/19/2003
row7 25 36 11/18/2003 6/19/2002
row8 37 48 11/18/2002 6/19/2001
row9 49 60 11/18/2001 6/19/2000

mp9 · Jun 13, 2006

In C4 (and then copy down to other rows):

=DATE(YEAR($A$1),MONTH($A$1)+$A4,DAY($A$1))

In D4 (and then copy down to other rows):

=DATE(YEAR($A$1),MONTH($A$1)+$B4,DAY($A$1))

[pc2]

http://www22.brinkster.com/accessory/

SkipVought · Jun 13, 2006

On the copy question,

1) use the [ignore][tt]...[/tt][/ignore] TGML tags to bracket your data.

2) remove the embedded TAB characters and replace with SPACE characters to alighn.

Skip,
_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...

a difference of A Pinion!}

Loomah · Jun 13, 2006

Going back to your first question, you could also have a look at the EDATE function, which is part of the analysis toolpak.

;-)

If a man says something and there are no women there to hear him, is he still wrong? [ponder]

How do I get the best answers?

macropod · Jun 13, 2006

Hi beadedbytes,

In order to deal with month ends correctly, mp9's formulae should be changed to:
=MIN(DATE(YEAR($A$1),MONTH($A$1)+$A4+1,0),DATE(YEAR($A$1),MONTH($A$1)+$A4,DAY($A$1)))
and
=MIN(DATE(YEAR($A$1),MONTH($A$1)+$B4+1,0),DATE(YEAR($A$1),MONTH($A$1)+$B4,DAY($A$1)))
respectively.

Cheers

[MS MVP - Word]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Adding Month(s) To a Date

beadedbytes

Technical User

mp9

Programmer

SkipVought

Programmer

Loomah

Technical User

macropod

Technical User

Similar threads

Part and Inventory Search

Sponsor