cancel
Showing results for
Did you mean:

## how to calculate time taking weekend, holiday and considering from 8 to 18.

```como calcular o tempo tirando final de semana, feriado e considerado apenas das 8 às 18.
Há casos que acontecem da noite para o dia, por exemplo:

Neste caso, entrou na sexta e saiu na segunda, para considerar 02:28 min, e na sexta são 2 horas para as 18 e na segunda saiu com 28 min. Saída de entrada 23/12/2021 16:00 25/12/2021 8:28
neste caso tenho que considero apenas 28min, pois o horário do horário das 8 às 18 eu desconsidero Saída de entrada 28/12/2021 18: 46 29/12/2021 8:28

Neste caso considere 3 horas Saída de entrada
27/12/2021 9:00 27/12/2021 12:00```
1 ACCEPTED SOLUTION

Accepted Solutions
2 REPLIES 2

Hi @Diego09

You need to have a Date table in which there is a column indicating whether a date is a working day or holiday. Then create a new column with below DAX to get the net work hours. You can download the attachment to see details. Hope it helps.

``````Net Work Hours =
var _day1 = MINX(FILTER('Date','Date'[Date]>='Table'[Check-In].[Date]&&'Date'[IsWorkingDay]=1),'Date'[Date])
var _day2 = MAXX(FILTER('Date','Date'[Date]<='Table'[Check-Out].[Date]&&'Date'[IsWorkingDay]=1),'Date'[Date])
var _nonWorkDays = COUNTROWS(FILTER('Date','Date'[Date]>_day1&&'Date'[Date]<_day2&&'Date'[IsWorkingDay]=0))
var _inDay_IsWorkDay = MAXX(FILTER('Date','Date'[Date]='Table'[Check-In].[Date]),'Date'[IsWorkingDay])
var _outDay_IsWorkDay = MAXX(FILTER('Date','Date'[Date]='Table'[Check-Out].[Date]),'Date'[IsWorkingDay])
var _inTime = IF(_inDay_IsWorkDay=1,TIMEVALUE(FORMAT('Table'[Check-In],"h:mm:ss")),TIME(8,0,0))
var _outTime = IF(_outDay_IsWorkDay=1,TIMEVALUE(FORMAT('Table'[Check-Out],"h:mm:ss")),TIME(18,0,0))
var _startTime = TIME(8,0,0)
var _endTime = TIME(18,0,0)
var _fullHours = (18-8)
var _day1Hours = MAX((_endTime-MAX(_inTime,_startTime))*24,0)
var _day2Hours = MAX((MIN(_outTime,_endTime)-_startTime)*24,0)
var _netWorkHours = IF(DATEDIFF(_day1,_day2,DAY)<0,0,(DATEDIFF(_day1,_day2,DAY)-1-_nonWorkDays)*_fullHours+_day1Hours+_day2Hours)
return
_netWorkHours``````

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.