Formatting dates in a text formula

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

All the date fields in my app are set to display as DD-MMM-YYYY. I have text formulas with that display several dates are they all display in MM-DD-YYYY format. How do I correct this?

Photo of Ben

Ben

  • 40 Points

Posted 3 years ago

  • 0
  • 1
Can you please post your current formula?
Photo of Ben

Ben

  • 40 Points
var date PS = [MS PS Spec Signed]; var date AS = [MS AS Spec Signed];
var date PF = [MS PF Spec Signed]; var date AF = [MS AF Spec Signed];
var user Assigned = [Study Design Analyst];

If(
 not IsNull($AS), "<div style=\"color:green;\">" & $AS & "</div>",
 not IsNull($PS) and $PS < Today(), "<div style=\"color:red;\">" & $PS & "</div>",
 not IsNull($PS), "<div style=\"color:#cca300;\">" & $PS & "</div>",
 "<div style=\"color:grey;\">Unscheduled</div>"
 )
&
If(
 not IsNull($AF), "<div style=\"color:green;\">" & $AF & "</div>",
 not IsNull($PF) and $PF < Today(), "<div style=\"color:red;\">" & $PF & "</div>",
 not IsNull($PF), "<div style=\"color:#cca300;\">" & $PF & "</div>",
 "<div style=\"color:grey;\">Unscheduled</div>"
 )
&
If(
 not IsNull($Assigned), "<div class=user>"&UserToName($Assigned)&"</div>",
  "<div class=user style=\"color:grey;\">Unassigned</div>"
)
Photo of Ben

Ben

  • 40 Points
One thing I did find that helps is ToFormattedText(Date,"DDMMYYYY"), but that still doesn't support MMM (JAN instead of 01).
I will try to post a solution tomorrow if time allows. We will need to create the text for the Date yourself.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Use script. I think it is crazy that QuickBase already uses the fantastic date.js library on many of its pages:

https://quickbase.intuitcdn.net/res/70580-2/js/date.js


which provides an amazing number of date manipulation methods as well as a generic date formatting function:

https://github.com/abritinthebay/datejs/wiki/Format-Specifiers


But the formula language has a relatively small set of Date manipulation and formatting options. 

So just use script for everything!
Photo of Richie Eyles

Richie Eyles

  • 92 Points 75 badge 2x thumb
So...First link is dead!

If the QB default is set to dd/mm/YYYY (for UK) it seems whenever you use the NOW formula (for instance) it returns the wrong date. So returns 7/9/2017 or 7 Sept when in fact it is 9 July. Can you point me in the right direction?
I have not tested this for syntax, but here is a shot at it for just one of your date variables.  Once that is working you can follow the pattern for the rest.

var date PS = [MS PS Spec Signed]; var date AS = [MS AS Spec Signed];


var date PF = [MS PF Spec Signed]; var date AF = [MS AF Spec Signed];
var user Assigned = [Study Design Analyst];



// PST will be the PS date in Text format.

var text PST = List("-",

Right("0" & ToText(Day($PS)),2),

Case(Month($PS),
1,"JAN",
2,"FEB",
3,"MAR",
4,"APR",
5,"MAY",
6,"JUN",
7,"JUL",
8,"AUG",
9,"SEP",
10,"OCT",
11,"NOV",
12,"DEC"),
ToText(Year($PS)));


If(
 not IsNull($AS), "<div style=\"color:green;\">" & $AS & "</div>",
 not IsNull($PS) and $PS < Today(), "<div style=\"color:red;\">" & $PST & "</div>",
 not IsNull($PS), "<div style=\"color:#cca300;\">" & $PST & "</div>",
 "<div style=\"color:grey;\">Unscheduled</div>"
 )
&
If(
 not IsNull($AF), "<div style=\"color:green;\">" & $AF & "</div>",
 not IsNull($PF) and $PF < Today(), "<div style=\"color:red;\">" & $PF & "</div>",
 not IsNull($PF), "<div style=\"color:#cca300;\">" & $PF & "</div>",
 "<div style=\"color:grey;\">Unscheduled</div>"
 )
&
If( 
 not IsNull($Assigned), "<div class=user>"&UserToName($Assigned)&"</div>", 
  "<div class=user style=\"color:grey;\">Unassigned</div>"
)
Photo of Ben

Ben

  • 40 Points
Never called a script from within a text formula, what's the syntax like for that using my example above?