Forum Discussion
How to calculate difference between two date/time in seconds excluding weekends and holidays .
Hi All, I need to calculate difference between this Created_date and Credit_hold_date columns in seconds by excluding weekends and holidays. Any help would be appreciated . sample d...
It worked by changing some code.
App_hold_holidays =COUNTROWS (FILTER ('Federal Holiday',AND ('Federal Holiday'[Date] >= MApproved_hold_ReleasedManually[Approved_date_time],'Federal Holiday'[Date] <= MApproved_hold_ReleasedManually[Credit_hold_date_time])))Thanks.
That makes things alot more simple.
You should start by creating a column counting the number of red days / weekends between the dates on each row.
HolidayCount =
VAR holdDate = 'Table1'[Credit hold date]
VAR createDate = 'Table1'[Created_date]
Return
Calculate(
Countrows('Table2'),
all('Table2'),
'Table2'[Date]<= holdDate ,
'Table2'[Date]>= createDate
)
Thanks , But My scope is to get the difference between those two columns in seconds by excluding holiday and weekend,
It was only the first step. We need that number to be able to know how many seconds to subtract in the next step.
Are you getting the correct number of red days for each row?Yes , How can i exclude weekends?
Difference in seconds;
Difference = (Table[Credit_Hold_Date] - Table[Created_Date]) * 24 * 60
Then subtract the seconds from the red days
Difference = ((Table[Credit_Hold_Date] - Table[Created_Date]) * 24 * 60) - (86400 * Table[HolidayCount])
Should give you the correct amount of seconds between the 2 dates, red days excluded.