Discussions

Expand all | Collapse all

Can I display a date field as YYYY-MM, where MM would be a two-digit rendering for the month?

  • 1.  Can I display a date field as YYYY-MM, where MM would be a two-digit rendering for the month?

    Posted 10-28-2013 19:45
    I'd like a field to look at a specific date entered, and then return the year/month in the following format: YYYY-MM, where MM would be two-digit rendering of the month. For example, 1/15/14 would return 2014-01, or 11/2/13 would return 2013-11.

    The following formula almost gets me there - except the month shows as a single digit, instead of having a leading 0 when needed.

    var number YYYY = Year([SLA Date:]);var number MM = Month([SLA Date:]);(ToText($YYYY)) & "-" & (ToText($MM))


  • 2.  RE: Can I display a date field as YYYY-MM, where MM would be a two-digit rendering for the month?

    Posted 10-28-2013 21:00
    No problem, Try this

    var text YYYY = Totext(Year([SLA Date:]));

    var text MM = right("0" & totext(Month([SLA Date:]),2);

    $YYYY & "-" & $MM)


    This trick is to zero pad the month, so you stick on a leading zero and then take the rightmost 2 characters.

    so, 0 becomes 01 and 12 becomes 012 which gets trimmed to 12 when you take the rightmost 2 characters.


  • 3.  RE: Can I display a date field as YYYY-MM, where MM would be a two-digit rendering for the month?

    Posted 05-12-2015 11:43
    The "trick is to zero pad the month"! I almost gave up in my search for the answer until I stumbled upon this - thanks Mark!


  • 4.  RE: Can I display a date field as YYYY-MM, where MM would be a two-digit rendering for the month?

    Posted 05-12-2015 11:52
    :) glad my posting from 2 years ago was useful.  I remember the first time someone on this forum showed me how to do that, so I'm just keeping the knowledge alive.


  • 5.  RE: Can I display a date field as YYYY-MM, where MM would be a two-digit rendering for the month?

     
    Posted 05-10-2019 02:47
    Yes, this is awesome... I ended up using two of your answers to get what I needed - YYMMDD
    Here's what I have:


    var date MyDate = ToDate([Date Created]);
    var text MM = Right("0" & ToText(Month(ToDate([Date Created]))),2);

    var text DD = Right("0" & ToText(Day($MyDate)),2);

    var text YY = Right(ToText(Year($MyDate)),2);




    $YY & $MM & $DD



  • 6.  RE: Can I display a date field as YYYY-MM, where MM would be a two-digit rendering for the month?

    Posted 05-14-2019 18:18
    Fun fact: you can use our new PadLeft and PadRight formulas to make sure you have the right number of 0's for your formula. So you could write the formula this way:

    var date MyDate = ToDate([Date Created]);
    var text MM = PadLeft(ToText(Month(ToDate([Date Created]))),2,"0");

    var text DD = PadLeft(ToText(Day($MyDate)),2,"0");

    var text YY = PadLeft(ToText(Year($MyDate)),2,"0");

    $YY & $MM & $DD

    More details in the Formula Functions Reference: https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=191&rl=i7">https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=191&rl=i7">https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=191&rl=i7