CODEWITHSUNDEEP
excelvba
GuilhermeAraujo
GuilhermeAraujo

Reputation: 35

How to get the month and format it to its written form

I have a date in "dd/mm/yy" format and I want to make it "dd of month of yyyy". The problem is I want it in Portuguese so it's "dd de month de yyyy" so if I do the normal way of formatting (```Format(Date, "dd de mmmm de yyyy")´´´) the "d" in the word "de" gets mixed up with the day.

How do I solve this?

I tried this

dados.Range("L" & linha).Value = Day(prcs.Range("B17")) & " de " & Format(Month(prcs.Range("B17")), mmmm) & " de " & Year(prcs.Range("B17"))

And what I get for a date like 12/05/18 is " 12 de 5 de 2018" instead of what I expected "12 de maio de 2018".

I also tried storing the month in another variable and formating it from there, but I either messed it up or it doesn't work.

Upvotes: 0

Views: 55

Answers (2)

FAB
FAB

Reputation: 2569

You want to give the format as a string "mmmm", i.e.: Format(Month(prcs.Range("B17")), "mmmm").

This would get highlighted if you had Option Explicit at the top of your code. enter image description here

Upvotes: 1

user11644679
user11644679

Reputation: 41

d is a reserved format mask character representing the day of the month without a leading zero. To use it explicitly as a literal string, escape it with a backslash or use quotes. Since you have to double up quotes within a quoted string, the backslash seems more appropriate.

'Format(Date, "dd \d\e mmmm \d\e yyyy")
dados.Range("L" & linha).Value = Date
dados.Range("L" & linha).NumberFormat = "dd \d\e mmmm \d\e yyyy"
'with quoted literal string
dados.Range("L" & linha).NumberFormat = "dd ""de"" mmmm ""de"" yyyy"

Upvotes: 1

Related Questions