Using a formula text field to colour a date, but keep the current day written

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
  • (Edited)
Currently I am using:

If([Due Date]<Today(), "<div style=\"background-color:yellow;\"> "&ToText([Due Date])&" </div>", ToText([Due Date]) )

Though, for example today would be displayed as 06/06/2017. Is it possible to accomplish this color change but i allow it to remain as "Tuesday" 06/06/2017?
Photo of Jordan

Jordan

  • 1,574 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
You mean to say you still want the text "Tuesday" to be part of the entire formula that is colored?
Photo of Jordan

Jordan

  • 1,574 Points 1k badge 2x thumb
Correct
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
So QB doesn't give you any easy way to convert the Date to the text value.  So you first have to get the Day value and then convert that to text, then include the text into your div.

DayOfWeek
 (Date d)

Description: Returns the number of days by which the given date d follows the first day of the week (Sunday returns 0).

Example: DayOfWeek([Start Date]) returns the number of the day of the week for the date that appears in the Start Date field.
DayOfWeek(ToDate("Aug 23, 2000")) returns 3
DayOfWeek(ToDate("Aug 20, 2000")) returns 0

Then a Case formula.  So your text variable might be:

var text DAY=Case(DayOfWeek([Due Date]),
0, "Sunday",
1, "Monday",
2, "Tuesday",
3, "Wednesday",
4, "Thursday",
5, "Friday",
6, "Saturday", "")

and combine that with your div to have:

If([Due Date]<Today(), "<div style=\"background-color:yellow;\"> $DAY, "&ToText([Due Date])&" </div>", "$DAY, "&ToText([Due Date]) )
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,532 Points 20k badge 2x thumb
>So QB doesn't give you any easy way to convert the Date to the text value.

You know this is very ironic considering that QuickBase uses two JavaScript date libraries (Moment.js and Date.js) for its own purposes and includes them on the pages you download:

https://assets.quickbasecdn.net/res/75635-25/js/moment.min.js

https://assets.quickbasecdn.net/res/75635-25/js/date.js

These libraries can each perform any type of date formatting, manipulation or calculation you could think of. Here are the docs for these two powerful libraries:

Moment.js
https://momentjs.com/

Date.js
http://www.datejs.com/

But native QuickBase only supports a limited hard-coded set of date features. Beyond those native date features you can configuration in the GUI you have to create your own formulas and there is a limited set of things you can do with the formulas.

But you can gain access to the features of either of these libraries using script. So while the particular requirement the OP had in this question can be solved with a formula, there is no date related issue that can't be solved using script using these two libraries which are already included in the pages you download and are cached in your browser.