cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft 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
Microsoft 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

Microsoft 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.
Microsoft 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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors