Forum Discussion

ashaikh's avatar
ashaikh
Helper III
8 years ago
Solved

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 bu...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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] )
    )

    time_calculation

    Best Regards,

    Dale

ashaikh's avatar
ashaikh
Helper III
8 years ago

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

 

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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] )
    )

time_calculation

Best Regards,

Dale