PDA

View Full Version : Solved: Format Date + 3 months



rob0923
08-22-2009, 12:56 PM
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
08-22-2009, 02:22 PM
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
08-22-2009, 02:37 PM
Maybe something like this:

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

rob0923
08-22-2009, 02:47 PM
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
08-22-2009, 03:04 PM
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
08-22-2009, 03:30 PM
With the date in A2

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

rob0923
08-22-2009, 04:54 PM
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?


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}))

rob0923
08-22-2009, 05:04 PM
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
08-24-2009, 06:09 PM
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
08-24-2009, 06:56 PM
is the 'pull down menu' data validation or a forms box or a control Toolbox box...

Bob Phillips
08-25-2009, 01:12 AM
Just use a DV with a list of 4 days and 8 days , and use



With Worksheets("Sheet1")

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

End With


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

rob0923
08-25-2009, 06:04 AM
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


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")

rob0923
08-28-2009, 06:20 PM
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
08-28-2009, 06:41 PM
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...



Plus12 = DateSerial(Year(Range("A1")), Month(Range("A1")) + 12, 0) + Day(Range("A1"))

Bob Phillips
08-29-2009, 02:06 AM
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
08-29-2009, 05:56 AM
Thanks! I'll test this out.

rob0923
08-31-2009, 09:41 AM
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
08-31-2009, 11:01 AM
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

rob0923
09-03-2009, 05:42 AM
This is working great, thanks for all your help!