Format date to DDMMYY with a text colour formula field

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
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>",
))))))




Photo of Dan G. (QB Support)

Dan G. (QB Support), Official Rep

  • 490 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Dan G. (QB Support)

Dan G. (QB Support), Official Rep

  • 490 Points 250 badge 2x thumb
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.