Stupid date format won't work

[TheGuyReturn]

Hi everyone

I'll get right to it, I can't seem to get the date in my macros to print in the format I'd like ("30 June 2017" for example)

Here's how I have gotten on so far:



Sub PrintEndOfLastMonthDate()

Selection.Delete Unit:=wdCharacter, Count:=1
Dim mBefore As Date
mBefore = Format(DateSerial(Year(Date), Month(Date), 0), "DD MMMM YY")
Selection.InsertBefore mBefore
Selection.MoveRight Unit:=wdCharacter, Count:=1

End Sub


This prints "30/06/2017" instead of "30 June 2017"

I reuse old documents as templates, ideally I'd just highlight the old date, hit a shortcut and be done with it.

I'm completely new to VBA.

I've been stuck on this for about half an hour now embarrassingly, would really appreciate some help


Thanks!

[SamT]

I would think that this would work

mBefore = Format(Date - 1, "d MMMM yyyy")

BTW, DateAdd("m", 0, Date - 1) means Add Zero months to yesterday

If you want the first of this month use

Format(Date, "1 MMMM yyyy")

For the first of last month use

Format(DateAdd("m", -1, Date), "1 MMMM yyyy")

[TheGuyReturn]

None of those work. I just want the last day of last month, printed as "30 June 2017" for example (my initial post was wrong, it's 2am here give me a break :crying:). :(

[SamT]

I wonder if any of them returned the first day o some month in the correct format.

Last day of last month. Place this code in a Standard Module for use anywhere. I suggesty naming that Module, "modCustumDateFunctions."

I have a slew of custom date functions in my so named Module. I just drag the module into whichever project needs one.
Option Explicit

Public Function LastDayOfLastMonth() As Date
LastDayOfLastMonth = DateAdd("D", -1, Format(Date, "mm/01/yyyy"))
End Function
Then, in your code use

Format(LastDayOfLastMonth, "dd MMMM yyyy")


You should also be able to use


mBefore = Format(DateAdd("d", -1, Format(Date, "mm/01/yyyy")), "dd MMMM yyyy")

[TheGuyReturn]

Still no luck, word seems to insist on printing "30/06/2017"

[SamT]

Review my edited post.

also... is that field preformatted as the wrong date format?

[TheGuyReturn]

Review my edited post.

also... is that field preformatted as the wrong date format?

Still no luck

and I'm not too sure what you mean, it's just a regular word document, date formatting like excel doesn't exist as far as i know

[gmayor]

mBefore is a string not a date. Change the line that defines the variable to

Dim mBefore As String
Your macro will then enter 30 June 17
If you want 30 June 2017
change the line as follows

mBefore = Format(DateSerial(Year(Date), Month(Date), 0), "DD MMMM YYYY")
Personally I would do it as follows

Sub PrintEndOfLastMonthDate()
'Graham Mayor - http://www.gmayor.com - Last updated - 02 Jul 2017
Dim oRng As Range
Set oRng = Selection.Range
oRng.Text = Format(DateSerial(Year(Date), Month(Date), 0), "DD MMMM YYYY ")
oRng.Collapse 0
oRng.Select
lbl_Exit:
Set oRng = Nothing
Exit Sub
End Sub

[TheGuyReturn]

.

Thanks a lot!

[gmaxey]

Johnny come late but why not simply:


Sub PrintEndOfLastMonthDate2()
Selection.Range.Text = Format(DateSerial(Year(Date), Month(Date), 0), "dd MMMM yyyy")
End Sub

[SamT]

Thanks Graham,

[gmayor]

Johnny come late but why not simply:


Sub PrintEndOfLastMonthDate2()
Selection.Range.Text = Format(DateSerial(Year(Date), Month(Date), 0), "dd MMMM yyyy")
End Sub


That would insert the date, but it would not put the cursor after the date, ready to continue typing, as did the original.