Date Calculation

Follow

Lundie Pinner

• October 13, 2014 14:24

I want to have one field that is a date and time, and I want to create a calculated field that adjusts that date and time by -11 hour. How would I write this in the calculation field?

0

• 

  Rainer Grabowski (FVM)

  • October 13, 2014 15:04

  Try something like this:

  moment(@datefield).lang('en').subtract(11, 'hour').format('YYYY-MM-DD h:mm a')

  In the format-term you can choose how you like the date format to be shown (order and format of day, month, year). "a" = am/pm vs. "A"=AM/PM

  Rainer

  3

  Comment actions Permalink
• 

  Mark Cannon

  • November 27, 2014 04:13

  Rainer, what is 'moment' in your calculation? I tried finding it at W3Schools.com and didn't find it.

  0

  Comment actions Permalink
• 

  Rainer Grabowski (FVM)

  • November 27, 2014 09:55

  Hi Mark, moment is a javascript library for handling with date/time values. See momentjs.com

  0

  Comment actions Permalink
• 

  Heather Fox

  • February 02, 2015 08:21

  Hi, Rainer. Thanks for the suggestion. My calculation isn't generating the correct hours based on that code.

  I have used: moment(@datefield).subtract(1, 'hour').format('MMM DD YYYY HH:MM')
  and also: moment(@datefield).lang('en').subtract(1, 'hour').format('YYYY-MM-DD h:mm a')

  Neither returns the expected value. Can you help?

  0

  Comment actions Permalink
• 

  Rainer Grabowski (FVM)

  • February 02, 2015 10:14

  Hi Heather,

  what do you expect and what do you get?

  Rainer

  0

  Comment actions Permalink
• 

  Heather Fox

  • February 02, 2015 10:58

  Thanks, Rainer, for replying. :)

  My referenced field is a Date field with a start time only in typical Podio format (eg. Mon 2 February 2015, 09:00).

  The calculated field formatting is perfect but the hour calculation is off.
  - My calculated field returns Feb 02 2015 00:02 and 2015-02-02 12:00 am respectively.
  - I would expect to see Feb 02 2015 08:00 and 2015-02-02 08:00 am.

  0

  Comment actions Permalink
• 

  Rainer Grabowski (FVM)

  • February 02, 2015 12:01

  Hi Heather,

  if you want to display the result as a text use:

  moment(@dateField).tz('America/New_York').format("MMM DD YYYY hh:mm a z")

  result: Feb 02 2015 08:00 am EST (z shows the timezone)

  If you want to display it in date format (like a date field) use:

  moment(@dateField).subtract(6,'hours').toDate()

  result: date and time in your usual date format (maybe you have to changed the value t0 be substracted; but be careful: It doesn't consider automatically DST changes)

  Rainer

  1

  Comment actions Permalink
• 

  Heather Fox

  • February 02, 2015 12:07

  Gold star, Rainer! I used the second instance and it works perfectly. So it was a timezone thing. :)

  Really appreciate your assistance.

  0

  Comment actions Permalink
• 

  Jonathan Peter

  • July 06, 2015 08:45

  Hi Rainer,

  There seems to be an issue when using your script:
  The type of result changed from number to date.
  When using: moment(@dateField).subtract(6,'hours').toDate()
  Especially the .toDate() part I guess.

  Removing it results with this error: The script returned an unsupported result type

  Any ideas?

  0

  Comment actions Permalink
• 

  Rainer Grabowski (FVM)

  • July 06, 2015 09:19

  Hi Jonathan,

  once saved calculation fields keep their format (number, text, date), it's fixed by saving. I assume, you have saved your calculation field when it had a number as result. You can't change that, you have to create a new fresh calculation field. If you want the result displayed as date use .toDate(), if as text use .format("your wanted format ") ; define the format inbetween the quotation marks (s. examples in the other postings)

  Rainer
  rg@delos-consulting.com

  1

  Comment actions Permalink
• 

  Jonathan Peter

  • July 06, 2015 10:56

  Works fine now, many thanks!

  0

  Comment actions Permalink

Please sign in to leave a comment.