Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Round Down Hour from a Date Field

Topic Labels: Formulas
Solved
Jump to Solution
2326 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Jarrad_Connor1
5 - Automation Enthusiast
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

TW Airtable Screen 01

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
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,
  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().

See Solution in Thread

4 Replies 4
Jarrad_Connor1
5 - Automation Enthusiast
5 - Automation Enthusiast

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

kuovonne
18 - Pluto
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,
  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().

Jarrad_Connor1
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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. 

genesis0792_0-1679541554697.png