Post

5 followers Follow
0
Avatar

Date Calculation

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?

Lundie Pinner

Please sign in to leave a comment.

11 comments

3
Avatar

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

Rainer Grabowski (FVM) 3 votes
0
Avatar

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

Mark Cannon 0 votes
0
Avatar

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

Rainer Grabowski (FVM) 0 votes
0
Avatar

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?

Heather Fox 0 votes
0
Avatar

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.

Heather Fox 0 votes
1
Avatar

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

Rainer Grabowski (FVM) 1 vote
0
Avatar

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

Really appreciate your assistance.

Heather Fox 0 votes
0
Avatar

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?

Jonathan Peter 0 votes
1
Avatar

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

Rainer Grabowski (FVM) 1 vote