Is this a bug in the calculation field?

Follow

ILE Systems Admin

• April 05, 2022 03:46

I just ran into this issue today. When referencing a date field in a calculation field I am getting the wrong value if I don't set the time component in the source field. The result in the calculation field is not respecting the local timezone. 

I recorded a loom video to demonstrate.

 

https://www.loom.com/share/7358b69104b843caba10b2d5c2d11bdc

0

• 

  Rainer Grabowski

  • April 06, 2022 10:20

  Hi Derek,

  that is a known issue. If no time value is entered Podio sets the time to 00:00 UTC but returns it in calculation field according to the timezone you set in your Podio account. This is annoying and and I don't know the technical reason for it either. But I created a piece of code that fixes this issue. It's a bit cumbersome but it works: 

  var d = @Your Date Field;
  var tz = "Europe/Berlin";
  var gmt = moment(d).tz(tz).format("ZZ");
  var gmtDiff = parseFloat(gmt)/100;
  var gmtDiff = Number(String(gmtDiff).replace(".3",".5"));
  var timeStamp0000 = moment(d).format("HH:mm") == "00:00" ? moment(d) : moment(d).add(gmtDiff,"h");
  var timeStampOwnTZ = timeStamp0000.toString().split("GMT").shift() + "GMT " + gmt;
  var asStringFormatted = moment(timeStamp0000).format("DD.MM.YYYY HH:mm");
  var asDate = moment(timeStampOwnTZ).tz(tz).toDate();
  asDate

  var tz should be your timezone ("Europe/Berlin" is my timezone).
  var asStringFormatted isn't necessary if you want to display the result as a date. It can be used if you need the output as a string with the correct date or date/time for a given timezone. For example if you need it for  PWA where you can't set timezones.  
  
  Rainer

  0

  Comment actions Permalink
• 

  Mary Ann Toms Entoma

  • June 03, 2022 19:03
  • Edited

  Hi Rainer,

  When I did that at first, referencing the date field the problem is still existing, because the source "date field" is not updating accurately even with calculation.  Here's how I resolved it, please refer to this link:  https://www.loom.com/share/8e138c3bdc3a46909be1a4d5a030cce6

  0

  Comment actions Permalink

Please sign in to leave a comment.