Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
New_be
Helper IV
Helper IV

Time Function: Round Up to Nearest 15 minutes

Hi everyone!😊

I have one problem here. I have time in & time out. And for time in i round up to the nearest 15 mins.
For the correct example;

TimeIn           TimeOutNewTimeIn
1/9/2020  6:47 am1/9/2020  3.05 pm1/9/2020 7:00 am

 

This table below is the problem example;

TimeInTimeOutNewTimeIn
1/9/2020  11.54 PM2/9/2020  7.08 AM?

 

So if 11.54 PM will be round up to nearest 15 mins, then the time in will be 12 am and the date will be 2/9/2020. So my data will be incorrect because its seems like this person work in 2 sept while he actually work in 1 sept.

 

So is there any formula that i can use to make something like;
          if TimeIn > 11: 30 PM then [TimeIn] else RoundUp(TimeIn)

 

 

RU_IT_1.PNG     

Example of my RoundUp column

 

RU_IT_2.1.PNG

Error rows

 

Thanks in advance!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@New_be , I think the best way is to take out the minute

 

Something like this

[Date] + #duration(0,0,15- Number.Mod(Time.Minute([Date]),15),0)

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@New_be , I think the best way is to take out the minute

 

Something like this

[Date] + #duration(0,0,15- Number.Mod(Time.Minute([Date]),15),0)

@amitchandak  so, is it correct from my understanding, time in is;


#duration(0,0,15 - Number.Mod(Time.Minute([InTime]),15)
at 11.46 PM, then 46 mod 15 will be 1. And then 15 mins - 1 = 14mins

 

[InTime] + #duration(0,0,15 - Number.Mod(Time.Minute([InTime]),15),0)

11.46 PM + 14 mins = 12:00 AM

 

So this way is same with roundUp to the nearest 15 mins or..? Can u please explain? Cause this is a very very good input for me 😀

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.