Skip to main content
Question

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

  • August 4, 2018
  • 0 replies
  • 75 views
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?


    This topic has been closed for replies.

    • DavidHawe
    • Quickbase Alumni
    • August 4, 2018
    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
    • Author
    • Quickbase Alumni
    • August 4, 2018
    Nope - still loose the leading 0 for the Month entry but thanks so much for trying.

    • RobIV
    • Quickbase Alumni
    • August 4, 2018
    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
    Terms & ConditionsAccessibility statement