Formatting date field in Calculations tables

Follow

Shanuj Patel

• May 17, 2017 15:57
• Edited

Hello,

I been having some difficulties trying to format dates when they appear in a table which was created in a calculation field.  

Here is the code:

var mix = @All of Invoice Number;
var bd = @All of Payment Status
var pd = @All of Payment Due Date
var dd = @All of Recieved Date
var bc = @All of Total Due (Autofill)
var bcsum = @Sum of Total Due (Autofill)
var lines = [];
for(var i = 0; i < mix.length;i++){
lines.push(mix[i] + " | " + bd[i] + " | " + pd[i] + " | " + dd[i] + " | " + bc[i]);
};
"INV | Status | Due date | Received date | Amount\n" +
"--- | --- | --- | --- | --- \n"+
lines.join("\n")+
"\n **Total** | | | | **" + bcsum + "**\n"
;

So the code work fine and the table works but the var pd  & var dd show the whole date including time/timezone. (see image one)

I want the format to be "DD MM YYYY". So I also tried using moment(@Date).format("YYYY MM DD") to correct this:

Code below: 

var mix = @All of Invoice Number;
var bd = @All of Payment Status
var pd = moment(@All of Payment Due Date).format("YYYY MM DD")
var dd = moment(@All of Recieved Date).format("YYYY MM DD")
var bc = @All of Total Due (Autofill)
var bcsum = @Sum of Total Due (Autofill)
var lines = [];
for(var i = 0; i < mix.length;i++){
lines.push(mix[i] + " | " + bd[i] + " | " + pd[i] + " | " + dd[i] + " | " + bc[i]);
};
"INV | Status | Due date | Received date | Amount\n" +
"--- | --- | --- | --- | --- \n"+
lines.join("\n")+
"\n **Total** | | | | **" + bcsum + "**\n"
;

However, the fields in the table then no longer show correctly (see image 2) 

If anybody know the solution to fix this, it would be must appreciated. 

Thanks,

 

0

• 

  Rainer Grabowski

  • May 18, 2017 08:15

  Hi,

  var pd and var dd are arrays with multiple dates in it. You can't format the array, only a single date (array element).
  Just do it in lines.push:

  moment(pd[i]).format("YYYY MM DD") 

  Rainer

  3

  Comment actions Permalink
• 

  Shanuj Patel

  • May 18, 2017 08:51
  • Edited

  Hi,

  That worked,

   Thanks for the help! 

  0

  Comment actions Permalink
• 

  Lars Skjoldby

  • October 30, 2017 22:31

  Hi @Rainer

  I'm having a bit of the same problem. I can print the full date with my array

  startDates[i] 

  
  I get this result: Tue Aug 01 2017 00:00:00 GMT+0000 (UTC)

  But if use the formating as you suggest:

  moment(startDates[i]).format("YYYY MM DD") 

   

  I get this result: Invalid date

   

  My code looks like this:

   

  var htype = @All of Type;
  var hrs = @All of Timer;
  var active = @All of Aktiv;
  var startDates = @All of Start dato with nulls;
  var lines = [];

  
  for (var i = 0; i < htype.length; i++){
  if (htype[i] == "Projekt timer" && active[i] != "Inaktiv"){
  lines.push(startDates[i]);
  lines.push( moment(startDates[i]).format("YYYY MM DD") );
  }
  };
  lines.join("\n");

   

  Do you know why?

  0

  Comment actions Permalink
• 

  Rainer Grabowski

  • October 31, 2017 22:21

  Hello Lars,

  when you pull the array with nulls (@All of Start dato with nulls) you get a nested array, each element is an own array (looks like [[item1], [item2],[],[item3] )

  Cause of it is nested, you have to get the elements in the loop by using [i][0] (not only [i]). 
  
  moment(startDates[i][0]).format("YYYY MM DD") 

  would work. But if there's a null value you will get the current date.
  I would do it like this

  var lines = [];
  for (var i = 0; i < htype.length; i++){
  if (htype[i] == "Projekt timer" && active[i] != "Inaktiv"){
  var date = startDates[i][0]  != null ? moment(startDates[i][0]).format("YYYY MM DD") : "No date";
  lines.push( date );
  }
  };
  lines.join("\n");

  Rainer

  2

  Comment actions Permalink
• 

  Lars Skjoldby

  • November 01, 2017 06:21

  Thanks Rainer. It works. :-)

  0

  Comment actions Permalink
• 

  Spencer Jones

  • January 10, 2019 15:30
  • Edited

  Thank you Rainier and everyone who commented. This was super-helpful!

  FYI, this is how I used it. It combines names from a related field, a date field, and an amount field.

  @All of Name.join() + "-" + moment(@Date).format("MM/DD/YYYY") + "-" + @Amount

  0

  Comment actions Permalink
• 

  Nathan Laughton

  • April 25, 2019 21:30

  Thanks Rainer! This has been bugging me for a while but now it makes total sense. The nested arrays were totally getting me! You've literally taught me so much JavaScript through all of your comments!

  0

  Comment actions Permalink

Please sign in to leave a comment.