Forum Discussion

vijaykumarj19's avatar
vijaykumarj19
Microsoft Employee
6 years ago

substract days from date excluding week ends

i have few columns c1        c2    c3          c4             c5           c6 1 21-08-2020    11     07-08-2020       22-10-2020       2-7-2020 2 10-08-2020     6     03-08-202...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

 

after reading the tables more carefully,try this function

 

 

 

let
  subWD = (d, nd) => 
    let
      pwe = Date.AddDays(Date.EndOfWeek(d, Day.Monday), - 7),
      chk = Duration.Days(d - pwe),
      nwein = (Number.IntegerDivide(nd - chk, 5) + Number.From(Number.Mod(nd - chk, 5) > 0))
    in
      Date.AddDays(d, - nd - nwein * 2 + 1)
in
  subWD

 

 

 

 

 

ps

this is a math-like solution: tangled but synthetic formulas.
A simpler and clearer but more verbose IT solution is possible by using  functions such as List.Dates, date.endofweek,
list. difference and few others.