Forum Discussion

ScottErvin's avatar
ScottErvin
Qrew Cadet
7 years ago

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

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?


Formulas and functions
  • DavidHawe's avatar
    DavidHawe
    Qrew Trainee
    7 years ago
    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])
    )
  • ScottErvin's avatar
    ScottErvin
    Qrew Cadet
    7 years ago
    Nope - still loose the leading 0 for the Month entry but thanks so much for trying.

  • RobIV's avatar
    RobIV
    Qrew Cadet
    7 years ago
    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