Skip to main content
Solved

Round Down Hour from a Date Field

  • November 18, 2022
  • 4 replies
  • 78 views
J
Forum|alt.badge.img+4

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

Best answer by kuovonne

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().

4 replies

J
Forum|alt.badge.img+4
  • Jarrad_Connor1
  • Author
  • Inspiring
  • November 18, 2022

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

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • November 19, 2022

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().

Soly
J
dmkirby
J
Forum|alt.badge.img+4
  • Jarrad_Connor1
  • Author
  • Inspiring
  • November 20, 2022

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

kuovonne
G
Forum|alt.badge.img+2
  • genesis0792
  • New Participant
  • March 23, 2023

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().


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. 

 

Terms & ConditionsAccessibility statement