cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

## Time calculation

Hello,

I am looking to do some time calculation on a DateTime and would want to know if there is an easier way to do it. What I am trying to do is basically calculate SLA DueTime which is same business day i.e 8hrs, Business day is 9am - 5pm which is 8hrs

But in our case how the same business day work is; if a ticket comes in anytime during the day then 8 hrs will be calculated as remaining business day during same day out of 8hrs and whats balance of 8 hrs will be on next day.

Below is the example of that.

DateTime column data - 6/27/2018 1:40 PM

Now for today till 5pm we have 3hrs 20mins, so out of 8hrs we are left with 4hr 40mins, So in this case SLA due date will be 6/28/2018 1:20 PM.

I would like understand how will I be able to do this calculation.

Any help will be appreciated.

Thanks

1 ACCEPTED SOLUTION
Employee

Hi @ashaikh,

Try this formula as a calculated column please.

```Column =
VAR currentDay =
DATEVALUE ( [Date] )
VAR nextWorkDay =
CALCULATE (
MIN ( 'Calendar'[Date] ),
FILTER (
'Calendar',
'Calendar'[Date] > currentDay
&& 'Calendar'[isWorkDay] = 1
)
)
RETURN
IF (
TIMEVALUE ( [Date] ) + TIME ( 8, 0, 0 )
<= TIME ( 17, 0, 0 ),
[Date] + TIME ( 8, 0, 0 ),
nextWorkDay + TIMEVALUE ( [Date] )
)
```

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Employee

I'm afraid I have no idea about how to get "1: 20PM".

1. 6/28/2018 9:00 AM + 3hrs 20 mins is 6/28/2018 0:20 PM;

2. 6/28/2018 9:00 AM + 4hrs 40 mins is 6/28/2018 1:40 PM;

So how can we get 6/28/2018 1:20 PM?

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

Yes you are right. It should be 1:40PM. So it will be 6/28/2018 1:40 PM

But the main point here which I wanted to show is SLA is 8 hrs and with in a business day so if 8 hrs is completed with in todays business date then Due Date will be next day with remaining time from start of business day i.e 8:00 am

Employee

Hi @ashaikh,

Try this formula as a calculated column please.

```Column =
VAR currentDay =
DATEVALUE ( [Date] )
VAR nextWorkDay =
CALCULATE (
MIN ( 'Calendar'[Date] ),
FILTER (
'Calendar',
'Calendar'[Date] > currentDay
&& 'Calendar'[isWorkDay] = 1
)
)
RETURN
IF (
TIMEVALUE ( [Date] ) + TIME ( 8, 0, 0 )
<= TIME ( 17, 0, 0 ),
[Date] + TIME ( 8, 0, 0 ),
nextWorkDay + TIMEVALUE ( [Date] )
)
```

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Employee

Could you please post the steps how we can get 1:20PM?

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

@v-jiascu-msft thats the calculation I am looking for. I have already mentioned how that number comes.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors