Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.
Nov 18, 2022 03:04 PM
Hello.
Looking for a formula that allows me to both convert to central time & round to nearest hour.
What I have
2022-11-11 4:01pm
2022-11-11 2:01pm
2022-11-11 12:02pm
2022-11-11 10:15am
Want
2022-11-11 4:00pm
2022-11-11 2:00pm
2022-11-11 12:00pm
2022-11-11 10:00am
Solved! Go to Solution.
Nov 18, 2022 04:26 PM
To truncate to the hour …
DATEADD(
{time},
-1 * MINUTE({time}),
"minutes"
)
If your time has seconds, you may also needs to do the same pattern to subtract seconds.
Note that this is different from rounding to the nearest hour. 10:59 would turn into 10:00, not 11:00. If you want to round to the nearest hour, that is a different formula.
IF(
MINUTE({time}) < 30,
DATEADD(
{time},
-1 * MINUTE({time}),
"minutes"
),
DATEADD(
{time},
60 - MINUTE({time}),
"minutes"
)
)
As for showing the time in Central time, you can use DATETIME_FORMAT() along with SET_TIMEZONE().
Nov 18, 2022 03:06 PM
oops… For to adjust the Want to central time. If anything just need to add an hour.
Nov 18, 2022 04:26 PM
To truncate to the hour …
DATEADD(
{time},
-1 * MINUTE({time}),
"minutes"
)
If your time has seconds, you may also needs to do the same pattern to subtract seconds.
Note that this is different from rounding to the nearest hour. 10:59 would turn into 10:00, not 11:00. If you want to round to the nearest hour, that is a different formula.
IF(
MINUTE({time}) < 30,
DATEADD(
{time},
-1 * MINUTE({time}),
"minutes"
),
DATEADD(
{time},
60 - MINUTE({time}),
"minutes"
)
)
As for showing the time in Central time, you can use DATETIME_FORMAT() along with SET_TIMEZONE().
Nov 20, 2022 10:34 AM
Worked. Absolutely part of the puzzle. Many Thank Yous.