Adding hours to a Date field

[Amanda_Lubow]

I am trying to add hours to a Date and Time field.

The formula I’m using is: DATEADD({Flight Date and Time},-{Time Prior},‘hour’)
I want the result to be the date and time, X hours before the original date ad time.

Flight Date and Time is a date field with time showing in a 12-hour format, and it’s exactly what it sounds like—the date and time of a flight.

Time Prior is a number field, and is the amount of time, in hours, I want the transportation to be on-site for the flight.

For example, .5 means 30 minutes prior. 3 means 3 hours prior.

So here’s what’s happening:

Flight Date and Time: 11/10/2022 4:00pm
Time Prior: 0.5
Result: 11/10/2022 9:20pm

It should be: 11/10/2022 3:30pm (.5 hours prior to the time)

Any ideas as to why this is happening?

[kuovonne]

Check the timezone settings on the formula field and make sure it matches the timezone settings of the original field.

You might also try -1 * {Time Prior} instead of -{Time Prior} If {Time Prior} is a calculated field, you might also want to check what the actual, unrounded value is.

[Amanda_Lubow]

I kept playing with it and what I had to do was set the Flight Date and Time Field and the result field to “Use the same time zone (GMT) for all collaborators.”

My concern is that when my computer changes time zones, these times will also change. Any idea if that’s the case? Or will they always display the same time as if the table is always in GMT?

[Amanda_Lubow]

Still having a formatting issue though.

I have this formula: {Hotel Name}&": “&DATETIME_FORMAT({Check In}, ‘mm/dd/yyyy’)&” - "&DATETIME_FORMAT({Check Out}, ‘mm/dd/yyyy’)

And the result I’m getting is: Hotel Name: 00/Fr/yyyy - 00/Mo/yyyy
It should be: Hotel Name: 4/8/2022 - 4/11/2022

Any ideas? I’ve tried messing with the time zone with no results.I have almost the exact same thing working on another table in the same base.

[Amanda_Lubow]

Ugh. Figured it out. The formatting for the dates needs to be capitalized. The following works:

{Hotel Name}&": “&DATETIME_FORMAT({Check In}, ‘MM/DD/YYYY’)&” - "&DATETIME_FORMAT({Check Out}, ‘MM/DD/YYYY’)