# How do I retain the leading 0 in a formula field coming from a date field?

• 0
• Question
• Updated 12 months ago
I have an Invoice date field but i need to create a new field where the date comes out as YYYY-MM.  I started with Year([Invoice Date])&"-"&Month([Invoice Date]) but the Month does not retain the leading 0 so sorting on reports does not work,  Next I tried var number YYYY = Year([Invoice Date]);var number MM = Month([Invoice Date]);(ToText(\$YYYY)) & "-" & (ToText(\$MM)) - same problem.  Finally I found this formula in another post but it has errors in it.  var text YYYY = Year([Invoice Date:]);var text MM = right("0" & ToText(Month([Invoice Date:]),2);\$YYYY & "-" & \$MM)

Can anyone help me with a solution?

• 328 Points

Posted 12 months ago

• 0

dwhawe, Champion

• 908 Points
Try this which is untested by me

If(
Month([Invoice Date])<10,
Year([Invoice Date])&"-0"&Month([Invoice Date]),
Year([Invoice Date])&"-"&Month([Invoice Date])
)
• 328 Points
Nope - still loose the leading 0 for the Month entry but thanks so much for trying.

• 1,070 Points
Try this slight variation:

(Formula Text Field)

If(
Month([Invoice Date])<10,
ToText(Year([Invoice Date])&"-0"&Month([Invoice Date])),
ToText(Year([Invoice Date])&"-"&Month([Invoice Date]))
)

Note: returns a text value, which I'm pretty sure you'll have to do to keep the leading '0'.

Thanks,

~Rob
(Edited)

dwhawe, Champion

• 908 Points
Yup, that is what I forgot.  Making it a text value
• 328 Points
Outstanding!  That did it!  Thank you so much.