# Round Down Hour from a Date Field

Topic Labels: Formulas
Solved
1303 4
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
18 - Pluto

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,
{time},
-1 * MINUTE({time}),
"minutes"
),
{time},
60 - MINUTE({time}),
"minutes"
)
)
``````

As for showing the time in Central time, you can use DATETIME_FORMAT() along with SET_TIMEZONE().

4 Replies 4
5 - Automation Enthusiast

oops… For to adjust the Want to central time. If anything just need to add an hour.

18 - Pluto

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,
{time},
-1 * MINUTE({time}),
"minutes"
),
{time},
60 - MINUTE({time}),
"minutes"
)
)
``````

As for showing the time in Central time, you can use DATETIME_FORMAT() along with SET_TIMEZONE().

5 - Automation Enthusiast

Worked. Absolutely part of the puzzle. Many Thank Yous.

4 - Data Explorer

I'm trying to do the exact same thing and it isn't working... Help?

The total time is = Prep Time + (End Time - Start Time)

I want to ROUNDUP the total time to the nearest quarter hour and was trying this formula to the nearest half hour and it doesn't work.