Solved: Format Date + 3 months

[rob0923]

Hi there,

Not sure if this possibe, but havn't seen it done as of yet. Is it possblie to have "Format(Date, "yyyy.mm.dd")" to get active date, but to also add 3 months manually on top of this format date?

Thanks,

[rbrhodes]

Hi,

You can easily add 'months' to a date using a variable to hold the result.

What you mean by months is important though. Do you mean 90 days? or same date, 3 'months' later eg: Jan 15 + 3 months = Apr 15.

You can be caught be the fact that a 'month' can be 28,29,30 or 31 days...


Note also that 'Format' returns a string variable.

[lucas]

Maybe something like this:

[VBA]Range("A3") = Format(CDate(DateSerial(Year(Date), Month(Date) + 3, Day(Date))), "yyyy mm dd ")[/VBA]

[rob0923]

Hi,

I am sure it would be 3 months from the day, so month by month would be fine. Not too worried about the actual day of the month. Thanks for all your inputs. I'll try it out as soon as I can.

[rbrhodes]

Hi rob,

I was just pointing out that if the date is Jan 31 and you add a 'month' you get March 3rd, etc as the date returned is 'x' days after day 'x' where 'x' is the original date.

In the case above x = 31 so addin a 'month' gives you 31 days later: Mar 3

[Bob Phillips]

With the date in A2

=MIN(DATE(YEAR(A2),MONTH(A2)+{4,3},DAY(A2)*{0,1}))

[rob0923]

Thanks, last thing I would need to know is if I have the value from A2. I will need to add the time in the macro. Can I paste something in a macro into a word bookmark?

[VBA]
Dim Date As Range
Dim objDoc As Object

Set Date = Worksheets("Sheet1").Range("A2") <--- Today Date

With objDoc.Bookmarks
.Item("FutureDate").Range.InsertAfter Format((Date.Value)MIN(DATE(YEAR(A2),MONTH(A2)+{4,3},DAY(A2)*{0,1}))
[/VBA]

[rob0923]

Hi all,

I will try to get this to work with what has been giving to me, but perferably I would like to just take the date that was inserted and have the macro add the 3 months and then insert it into a word bookmark. Not sure if its gonna work, but thanks for everyones help!

[rob0923]

Hi There,

One last shot at this. If I don't have the option of insert a conversion into an excel work book and need the macro to calculate it for me. What I have to work with is a manually entered date mm/dd/yyyy in Cell A2 and I have a Pull Down Menu with 4 days and 8 days (Notice they do have the word days behind it) I would like the macro to remove the word days for the conversion and add the 4 or 8 days to the date that was manually entered. but the macro would need to add and convert for a word bookmark.

Thanks!

[rbrhodes]

is the 'pull down menu' data validation or a forms box or a control Toolbox box...

[Bob Phillips]

Just use a DV with a list of 4 days and 8 days , and use

[vba]

With Worksheets("Sheet1")

mDate = .Range("A2").Value + Replace(.Range("B2").Value, " days", "")

End With
[/vba]

and use the value of mDate in the formula instead of A2

[rob0923]

Hi, The pull down menu is Data Validation. The other problem I think I am going to have is that I am wanting to copy a date value and I have it being copied as text. Can I use the string that Lucas mentioned

[VBA]
Dim VarDate As Range
Dim mDate

With Worksheets("Sheet1")

VarDate = .Range("A1").Value
mDate = .Range("A2").Value + Replace(.Range("A2").Value, " days", "")

End With

Range("A3") = Format(VarDate(DateSerial(Year(Date), Month(Date), Day(Date) + mDate)), "Long Date") [/VBA]

[rob0923]

Hi, I keep running into problems with "dates"

I needs two dates, one being with days which xld solved by removing the word days and using the value from the dv using dateserial, or dateadd.

The next one is tricky. I have a dv again with 12 and 24 values (months) but I would like it to add 12 or 24 months to the date, but as rbrhodes has mentioned not all months have 30 days which throws the off the day for the next month and I have know clue how to intergrate xld's =MIN code into the vba because I have know clue how it calculates! I am most likely going to use "cdate" to calculate the formula.

Thanks for all the help already given on this topic.

[rbrhodes]

Hi,

Here's a formula from cPearsons' website (adapted)

Given a date in Cell A1

So to add 12 months it would be:

=DATE(YEAR(A1),MONTH(A1)+12,0)+ DAY(A1)

Easily changed for 24 months or into vba...


[VBA]
Plus12 = DateSerial(Year(Range("A1")), Month(Range("A1")) + 12, 0) + Day(Range("A1"))
[/VBA]

[Bob Phillips]

The next one is tricky. I have a dv again with 12 and 24 values (months) but I would like it to add 12 or 24 months to the date, but as rbrhodes has mentioned not all months have 30 days which throws the off the day for the next month ...

If you are adding 12 or 24 months there is nor problem because the resultant date will always be the same month as the start date, just 1 or 2 years forward, so it is bound to have the same number of days, except of course 29th Feb in a leap year. You could always test for that at the start and move it back one day.

[rob0923]

Thanks! I'll test this out.

[rob0923]

Hi,

How would you test for leap years. I have found some code for leap years for true or false statements, but nothing that will add or subtract days if a lear year is detected.

[Bob Phillips]

[vba]

If (Year(dte) \ 4) * 4 = Year(dte) Then

If ((Year(dte) \ 100) * 100 <> Year(dte)) Or ((Year(Date) \ 400) * 400 = Year(dte)) Then

MsgBox "Leap year"
End If
End If
[/vba]

[rob0923]

This is working great, thanks for all your help!