Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 data : 

table 1 

Created_dateCredit_hold_date
5/07/19 09:52:27 AM5/07/19 12:09:07 PM
5/21/19 08:24:52 AM5/21/19 02:14:12 PM
6/11/19 02:03:33 PM6/11/19 04:36:44 PM
5/23/19 04:39:14 PM5/29/19 01:58:15 PM
5/29/19 11:43:57 AM5/30/19 02:19:15 PM
5/30/19 03:15:07 PM6/03/19 05:05:27 PM
7/04/19 03:10:49 PM7/05/19 09:44:30 AM
6/28/19 04:01:49 PM7/02/19 09:04:33 AM

 

 

Table 2

DateHolidays
01/01/2019New Year's Day
02/18/2019Family Day
04/19/2019Good Friday
05/20/2019Victoria Day
07/01/2019Canada Day(observed)
08/05/2019Civic Holiday
09/02/2019Labour Day
10/14/2019Thanksgiving Day
12/25/2019Christmas Day
12/26/2019Boxing Day

 

Regards,

Khushboo

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

9 Replies

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Can the created/hold date occur on a holiday/weekend?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    No .

     

    While calculating difference between created/hold date it should exclude weekends and holidays.

    • tex628's avatar
      tex628
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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
      )

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks , But My scope is to get the difference between those two columns in seconds by excluding holiday and weekend,