Forum Discussion

PaulEaston's avatar
PaulEaston
Qrew Assistant Captain
5 years ago

Displaying Date field dates without dashes

I want to generate a good that looks like this: REQ##-YYYYMMDD-###### where REQ[Record ID#]-[From a Date Field]-[From a number field] e.g.: REQ12-20210218-123456 The formula I used in the fo...
Formulas and functions
PaulEaston's avatar
PaulEaston
Qrew Assistant Captain
5 years ago
Okay, this is how I got it to work: 

"REQ"&[Record ID#]&"-"&Part(ToFormattedText([Date Received],"YYYYMMDD"),1,"-")&Part(ToFormattedText([Date Received],"YYYYMMDD"),2,"-")&Part(ToFormattedText([Date Received],"YYYYMMDD"),-1,"-")&"-"&[Source ID]


Probably a more elegant way to accomplish this, but this works.

------------------------------
Paul Easton
------------------------------
BlakeHarrison's avatar
BlakeHarrison
Qrew Captain
5 years ago
Rather than using the 'Part' function against a date field, you can use 'Month', 'Day', and 'Year'. I'd do something like this:
var number y = Year([Date Received]);
var text m = Right(ToText(100+Month([Date Received])),2);
var tex d = Right(ToText(100+Day([Date Received])),2);

"REQ" & List("-",[Record ID#],$y & $m & $d,[Source ID] ​


------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quickbase Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
------------------------------
  • PaulEaston's avatar
    PaulEaston
    Qrew Assistant Captain
    5 years ago
    Thank you Blake.  Is there any disadvantage to use part, besides it being a bit of kludge?  Any performance impacts?  Just curious.

    ------------------------------
    Paul Easton
    ------------------------------
    • BlakeHarrison's avatar
      BlakeHarrison
      Qrew Captain
      5 years ago
      Not in the grand scheme of things, no. Your formula accomplishes the same thing and in just about the same amount of formatting gymnastics. Your use of 'ToFormattedText' and 'Part' is fairly interchangeable with my option to add 100 to the Month/Day, convert it to text, and then pull off the last 2 characters using 'Right'.

      ------------------------------
      Blake Harrison
      bharrison@datablender.io
      DataBlender - Quickbase Solution Provider
      Atlanta GA
      404.800.1702 / http://datablender.io/
      ------------------------------