Between Two Dates - Calculate Hours/Minutes for Weekends if Applicable?

[lager1001]

I have two dates: START DATE/TIME and END DATE/TIME. I need to determine how many hours/minutes between these two values are exclusive to Saturdays and Sundays ONLY.

For a simple example: START DATE/TIME: 07/10/2020 12:00PM END DATE/TIME: 07/13/2020 12:00PM.

This started on a Friday at noon and ended on a Monday at noon so 48 hours of Saturday and Sunday would be in this range.

Next Example:

START DATE/TIME: 07/11/2020 8:00AM END DATE/TIME: 07/13/2020 10:00AM

This started on Saturday and ended on a Monday. so this has 40 hours falling on Saturday and Sunday within the range.

Can anyone assist?

 

[Matt Allington]

Read my 2 articles, starting here Calculating Business Hours Using DAX - Excelerator BI

 

[lager1001]

Thanks for the article. I've looked at it and several others online but I'm not getting the results I need. I need this calculated down to the minute. My example above is in whole hours but minutes really matter here. If a Saturday or Sunday falls within the date range I need to exclude the hours and minutes for any time in the date/time duration that fell on Saturday or Sunday. For instance, there are several dates in the table that Start or End on a Saturday or Sunday, so it's very broken up and may only include a few hours of Saturday or Sunday in the range. I will keep researching. Thank you.

 

[billszysz]

Hi lager
Try this code below (i think it will meet your expectations.

	Source Table			Result
	START DATE/TIME	END DATE/TIME		START DATE/TIME	END DATE/TIME	SatSun duration
	2020-07-11 10:32:15	2020-07-13 17:45:27		2020-07-11 10:32	2020-07-13 17:45	37:27:45
	2020-07-11 10:32:15	2020-07-11 17:45:27		2020-07-11 10:32	2020-07-11 17:45	7:13:12
	2020-07-11 10:32:15	2020-07-12 17:45:27		2020-07-11 10:32	2020-07-12 17:45	31:13:12
	2020-07-12 17:45:27	2020-07-19 17:40:31		2020-07-12 17:45	2020-07-19 17:40	47:55:04
	2020-07-12 17:45:27	2020-07-20 17:40:31		2020-07-12 17:45	2020-07-20 17:40	54:14:33
	2020-07-12 17:45:27	2020-07-18 13:20:12		2020-07-12 17:45	2020-07-18 13:20	19:34:45
	2020-07-13 17:45:27	2020-07-17 13:20:12		2020-07-13 17:45	2020-07-17 13:20	0:00:00
	2020-07-11 13:30:05	2020-07-29 14:23:07		2020-07-11 13:30	2020-07-29 14:23	130:29:55

let
   fnSatSun = (s as datetime, e as datetime) => 
      let
         fnCount = (lst as list) => 
            let
               NsDate = Number.From(StartDate),
               NeDate = Number.From(EndDate),
               Transform = List.Transform(
                  lst, 
                  each 
                     if StartDate = EndDate
                     then DateTime.Time(e) - DateTime.Time(s)
                     else 
                        if NsDate = _
                        then Duration.From(1 - Number.From(DateTime.Time(s)))
                        else 
                           if NeDate = _
                           then Duration.From(Number.From(DateTime.Time(e)))
                           else #duration(1, 0, 0, 0)
               ),
               Total = List.Sum(Transform)
            in
               Total,

         StartDate = DateTime.Date(s),
         EndDate = DateTime.Date(e),
         LST = {Number.From(StartDate)..Number.From(EndDate)},
         SatSun = List.Select(LST, each Number.Mod(_, 7) < 2),
         Count = List.Count(SatSun),
         Result = if Count = 0 then #duration(0, 0, 0, 0) else fnCount(SatSun)
      in
         Result,

   Source = Excel.CurrentWorkbook(){[Name = "TimeTbl"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(
      Source, 
      {{"START DATE/TIME", type datetime}, {"END DATE/TIME", type datetime}}
   ),
   Result = Table.AddColumn(
      #"Changed Type", 
      "SatSun duration", 
      each fnSatSun([#"START DATE/TIME"], [#"END DATE/TIME"])
   )
in
   Result