Discussions

Expand all | Collapse all

Format date to DDMMYY with a text colour formula field

  • 1.  Format date to DDMMYY with a text colour formula field

    Posted 03-02-2017 15:56
    Original post:
    • Asked by Sarah
    • a month ago

    Hi

    I have created a formula text field to reference a date field.  The formula below colours the new field depending on whether certain conditions are met or not.  My dates in the App are all set to DDMMYY however in this new field the colours are fine but the date displays as MMDDYY and I can't get it to change to DDMMYY.  I have tried to add ToFormattedText(Date(2000,1,30),"DDMMYY" to my formula,  however it keeps erroring and I'm not sure where in my formula to put it so it works. I'm also not sure whether I need to change the 200,1,30.  Any ideas?

    Many thanks

    Sarah

    If(not IsNull([Launch Date Actual]), "<div style=\"background-color:#FFFFFF;\">"& [Launch Date Forecast]&"</div>",
    If([Launch Date Forecast]<Today(), "<div style=\"color:white; background-color:#FF6A6A;\">"& [Launch Date Forecast]&"</div>",
    If([Launch Date Forecast]=Today(), "<div style=\"background-color:#FFE75F;\">"& [Launch Date Forecast]&"</div>",
    If([Launch Date Forecast]>Today(), "<div style=\"background-color:#98FB98;\">"& [Launch Date Forecast]&"</div>",
    ))))))






  • 2.  RE: Format date to DDMMYY with a text colour formula field

    Posted 03-02-2017 15:57
    Answer

    You could always try setting up a var for each piece of the date similar to this:

    var text MM = Right(ToText(Month([date field])+100),2);

    This would grab the MM piece and you would do similar formulas for DD and YY and then just connect them like this:

    $DD & $MM & $YY


    Was this answer helpful?

    OriginalAnswer posted by:
    Blake Harrison - DataBlender , Data Mixologist
    QuickBase Solution Provider a month ago



    1 comment

    Thanks Blake. I found how to use ToFormattedText( [date field],"DDMMYYYY") and where to put it in my formula which was super simple.