Discussions

Expand all | Collapse all

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

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

    Posted 08-03-2018 21:20
    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?




  • 2.  RE: How do I retain the leading 0 in a formula field coming from a date field?

    Posted 08-03-2018 22:23
    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])
    )


  • 3.  RE: How do I retain the leading 0 in a formula field coming from a date field?

    Posted 08-03-2018 23:05
    Nope - still loose the leading 0 for the Month entry but thanks so much for trying.



  • 4.  RE: How do I retain the leading 0 in a formula field coming from a date field?

     
    Posted 08-03-2018 23:36
    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


  • 5.  RE: How do I retain the leading 0 in a formula field coming from a date field?

    Posted 08-04-2018 00:21
    Yup, that is what I forgot.  Making it a text value


  • 6.  RE: How do I retain the leading 0 in a formula field coming from a date field?

    Posted 08-04-2018 13:42
    Outstanding!  That did it!  Thank you so much.