How to add 6 months and minus 1 day from a date?

[TeedyKay]

I've been tasked at work to come up with a new workbook for our qualification of employees. Their qualification lasts 6 months minus 1 day

EG
Date Of Test = 16/01/11
Expiry Date = 15/07/11

Could anyone please help me with the formula to achieve the generation of an Expiry Date after inputting the Test Date?

I have tried the following:

=DATE(YEAR(TEST DATE),MONTH(TEST DATE)+6,DAY(TEST DATE)-1)

as recommended on a few sites and forums I have visited, however it has some major issues with it, it doesn't appear to be consistent.

Any help on this issue would be greatly appreciated.

 

[Jonmo1]

Welcome to the board...

You may have missplaced the -1
=DATE(YEAR(TEST DATE),MONTH(TEST DATE)+6,DAY(TEST DATE)-1)

Try like this instead

=DATE(YEAR(TEST DATE),MONTH(TEST DATE)+6,DAY(TEST DATE))-1

There's a big difference between the two.

 

[Jonmo1]

Or if you have the Analysis Toolpack from Tools - Addins

=EDATE(TEST DATE,6)-1


Perhaps the difference between the location of the -1 was not as huge as I initially thought.

 

[TeedyKay]

Alas, the analysis toolpack is not available to me at my place of work.

Attempted the change in the -1 but it still doesn't appear to be happening, for instance

TEST DATE = 01/08/11

EXP DATE = 31/01/12

 

[barry houdini]

TEST DATE = 01/08/11

EXP DATE = 31/01/12

Isn't that right? Add 6 months to 1st August and you get 1st Feb in the following year - minus 1 day and that gives you 31st Jan......

 

[tez_field]

Ive not fully tested it but give this a go:

Public Function add6months(start_date As Date)

    Dim months(1 To 12) As Integer
    months(1) = 31
    months(2) = 28
    months(3) = 31
    months(4) = 30
    months(5) = 31
    months(6) = 30
    months(7) = 31
    months(8) = 31
    months(9) = 30
    months(10) = 31
    months(11) = 30
    months(12) = 31
    
    If Month(start_date) <= 6 And Year(start_date) Mod 4 = 0 Then months(2) = 29
    If Month(start_date) > 6 And Year(start_date) Mod 4 = 3 Then months(2) = 29
    
    For i = 1 To 6
        start_date = start_date + months(Month(start_date))
    Next i
    
    add6months = start_date - 1
End Function