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

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
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?


Photo of Scott

Scott

  • 238 Points 100 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of dwhawe

dwhawe, Champion

  • 662 Points 500 badge 2x thumb
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])
)
Photo of Scott

Scott

  • 238 Points 100 badge 2x thumb
Nope - still loose the leading 0 for the Month entry but thanks so much for trying.

Photo of Rob White IV

Rob White IV

  • 948 Points 500 badge 2x thumb
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)
Photo of dwhawe

dwhawe, Champion

  • 662 Points 500 badge 2x thumb
Yup, that is what I forgot.  Making it a text value
Photo of Scott

Scott

  • 238 Points 100 badge 2x thumb
Outstanding!  That did it!  Thank you so much.