Forum Discussion

flaviobdsti's avatar
flaviobdsti
Icon for Helper I rankHelper I
2 years ago
Solved

Seeking Assistance with Date/Time Calculations and Excluding Holidays and Non-Productive Hours

Hello, everyone. I've been struggling for a few days to solve an issue involving date/time calculations. If anyone can help me, I'll be grateful.

I have a table called dimTime, which was generated from another table. I have two columns in the date/time format called Start and End. I need to know how many minutes are between the two dates, taking into account the employee's working hours, which are from 08:00:00 to 17:00:00, and excluding Saturdays and Sundays.

Can you help me? I've searched, conducted several simulations, looked in the ChatGPT, and still couldn't find a solution.
I appreciate it in advance.

dimTime


dimHoliday


Productive work interval: 08:00:00 to 17:00:00



Need Help
  • Dangar332's avatar
    Dangar332
    2 years ago

    Hi, flaviobdsti 

    in earlier post it some mistake 

    as i understood below is your solution

    total minute = 
var a = 
     var a1 = FORMAT(TIME(17,00,00)-FORMAT('Table (5)'[start],"hh:MM:ss"),"hh:MM:ss")
     var a2 = HOUR(a1)*60 +MINUTE(a1) +(SECOND(a1)/100)
     return a2
var b =
     var b1 = FORMAT(FORMAT('Table (5)'[end]-TIME(8,00,00),"hh:MM:ss"),"hh:MM:ss")
     var b2= HOUR(b1)*60 +MINUTE(b1) +(SECOND(b1)/100)
     RETURN b2
var c = a+b
var d= 
       var d1 =FORMAT('Table (5)'[end]-'Table (5)'[start],"hh:mm:ss")
       var d2 = HOUR(d1)*60+MINUTE(d1)+(SECOND(d1)/100)
       RETURN d2
var e = NETWORKDAYS('Table (5)'[start],'Table (5)'[end],1,ALL('Table (5)'[holiday]))
var f =c+ ((e-1)*9*60)
RETURN
SWITCH(TRUE(),
        e=1,d,
        e=2,c,
        e>2,f)

     

     

5 Replies

    • flaviobdsti's avatar
      flaviobdsti
      Icon for Helper I rankHelper I
      2 years ago

      Thank you for the response; I had already used this function, but it was not enough to solve the problem. The thing is, the NETWORKDAYS function is a function that calculates the number of business days (working days) between two dates, excluding Saturdays and Sundays by default. However, in my case, I need to return the value in minutes and consider a specific range of working hours. With the NETWORKDAYS function alone, I haven't been able to achieve the expected result.

      • Dangar332's avatar
        Dangar332
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

        Hi, flaviobdsti 

         

        minute = 
 var a = NETWORKDAYS('Table (4)'[start],'Table (4)'[end])
 var starttime = FORMAT('Table (4)'[start],"hh:mm:ss")
 var endtime = FORMAT('Table (4)'[end],"hh:mm:ss")
 var _hour = LEFT(FORMAT(endtime-starttime,"hh:mm:ss"),2)*60
 var _minute = RIGHT(LEFT(FORMAT(endtime-starttime,"hh:mm:ss"),5),2)
 var _second = RIGHT(FORMAT(endtime-starttime,"hh:mm:ss"),2)
 var result = _hour +(_minute &"."&_second)
 return
 result

         

         

         

         

         

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi, flaviobdsti 

    in earlier post it some mistake 

    as i understood below is your solution

    total minute = 
var a = 
     var a1 = FORMAT(TIME(17,00,00)-FORMAT('Table (5)'[start],"hh:MM:ss"),"hh:MM:ss")
     var a2 = HOUR(a1)*60 +MINUTE(a1) +(SECOND(a1)/100)
     return a2
var b =
     var b1 = FORMAT(FORMAT('Table (5)'[end]-TIME(8,00,00),"hh:MM:ss"),"hh:MM:ss")
     var b2= HOUR(b1)*60 +MINUTE(b1) +(SECOND(b1)/100)
     RETURN b2
var c = a+b
var d= 
       var d1 =FORMAT('Table (5)'[end]-'Table (5)'[start],"hh:mm:ss")
       var d2 = HOUR(d1)*60+MINUTE(d1)+(SECOND(d1)/100)
       RETURN d2
var e = NETWORKDAYS('Table (5)'[start],'Table (5)'[end],1,ALL('Table (5)'[holiday]))
var f =c+ ((e-1)*9*60)
RETURN
SWITCH(TRUE(),
        e=1,d,
        e=2,c,
        e>2,f)