Forum Discussion

adoster's avatar
adoster
Resolver I
3 years ago
Solved

Rounding to nearest 30 min Time. Code errors out if time is after 11:30PM rounding to next day.

Hello. I am using the following code to create a new column to round to the nearest 30 minutes.   = Table.AddColumn(#"Added Conditional Column", "RoundedTime", each Time.From(Number.RoundUp(48*Numb...
Need Help
  • artemus's avatar
    artemus
    3 years ago

    Oh, sorry about that (it is a bit difficult to double check the syntax when I don't have the full query). This should be the correct version:

    Table.AddColumn(#"Added Conditional Column", "RoundedTime", each Time.From(Number.Mod(Number.RoundUp(48*Number.From(Time.From([TABLE_DATE_TIME]))/1)/48, 1)), type time)

     

    Note: When I ran the first query I put, I got a different error.

collinsg's avatar
collinsg
Solution Sage
3 years ago

Here is alternative. It works by

  • Calculating how many half hours have elapsed since 00:00:00 on 1/1/1 until the datetime being rounded.
  • It rounds up that number of half hours.
  • It multiples the rounded up number by a duration of half an hour.
  • It adds that to 1/1/1 00:00:00.
= Table.AddColumn(
   #"Name of Previous Step",
   "Rounded Time",
   each
      #datetime(1,1,1,0,0,0) +
      #duration(0,0,30,0) * Number.RoundUp ( 
         ( [Time to Round] - #datetime(1,1,1,0,0,0) ) / #duration(0,0,30,0) 
      ),
   type datetime
)