Forum Discussion
Anonymous
4 years agoNot applicable
Networkdays
Hi All, I'd like to know if there is a way to calculate networkdays between two different days with time i.e. [Creation Date] is 11/23/2021 3:30 pm [Completion Date] is 12/06/2021 9:04 am a...
- 4 years ago
// Something like this. // Dates is a calendar that stores // full days (with no time component). |---------|=====A=====|==========|---------|==========|---------|==========|----B----| // Legend: |====| -> non-working day, |---| -> working day var DateTime1 = A var Date1 = DATEVALUE( DateTime1 ) var DateTime2 = B var Date2 = DATEVALUE( DateTime2 ) var FirstWorkingAfterOrOnA = CALCULATE( MIN( Dates[Date] ), Dates[Date] >= Date1, Dates[Day Type] = "working" ) var LastWorkingBeforeOrOnB = CALCULATE( MAX( Dates[Date] ), Dates[Date] <= Date2, Dates[Day Type] = "working" ) var StartDate = MAX( DateTime1, FirstWorkingAfterOrOnA ) var EndDate = MIN( DateTime2, LastWorkingBeforeOrOnB ) var NonworkingDays = CALCULATE( COUNTROWS( Dates ), Dates[Date] >= StartDate, Dates[Date] <= EndDate, Dates[Day Type] = "non-working" ) var NetworkingDays = 1 * (EndDate - StartDate) - NonworkingDays var Result = IF( NetworkingDays > 0, NetWorkingdays ) return Result
Anonymous
4 years agoNot applicable
This would not help . Networkdays are working days.
daXtreme
4 years agoSolution Sage
// Something like this.
// Dates is a calendar that stores
// full days (with no time component).
|---------|=====A=====|==========|---------|==========|---------|==========|----B----|
// Legend: |====| -> non-working day, |---| -> working day
var DateTime1 = A
var Date1 = DATEVALUE( DateTime1 )
var DateTime2 = B
var Date2 = DATEVALUE( DateTime2 )
var FirstWorkingAfterOrOnA =
CALCULATE(
MIN( Dates[Date] ),
Dates[Date] >= Date1,
Dates[Day Type] = "working"
)
var LastWorkingBeforeOrOnB =
CALCULATE(
MAX( Dates[Date] ),
Dates[Date] <= Date2,
Dates[Day Type] = "working"
)
var StartDate =
MAX( DateTime1, FirstWorkingAfterOrOnA )
var EndDate =
MIN( DateTime2, LastWorkingBeforeOrOnB )
var NonworkingDays =
CALCULATE(
COUNTROWS( Dates ),
Dates[Date] >= StartDate,
Dates[Date] <= EndDate,
Dates[Day Type] = "non-working"
)
var NetworkingDays =
1 * (EndDate - StartDate) - NonworkingDays
var Result =
IF( NetworkingDays > 0,
NetWorkingdays
)
return
Result