Forum Discussion
Negative difference in Days
HI,
I have the below to calculate Network days between two dates. However if it is a negative, it isn't returning the value of that, it just says ERROR. I can't figure out why, have I missed anything to get a negative number?
each List.Count(List.Select(List.Dates([KPI_DATE], Duration.TotalDays([REC_DATE]-[KPI_DATE]) + 1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_)))))
Thanks.
Hi Rich_Wyeth ,
Try the following code:
try List.Count(List.Select(List.Dates([KPI_DATE], Duration.TotalDays([REC_DATE]-[KPI_DATE]) + 1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_)))) otherwise - List.Count(List.Select(List.Dates([KPI_DATE], Duration.TotalDays([KPI_DATE]-[REC_DATE]) -1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))))
3 Replies
Hi Rich_Wyeth ,
Try the following code:
try List.Count(List.Select(List.Dates([KPI_DATE], Duration.TotalDays([REC_DATE]-[KPI_DATE]) + 1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_)))) otherwise - List.Count(List.Select(List.Dates([KPI_DATE], Duration.TotalDays([KPI_DATE]-[REC_DATE]) -1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))))It appears from your formula you are not taking Holidays into account. Given that,
here is one way of doing this that returns the same results as NETWORKDAYS in Excel:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31TcyMDJT0lEy0reAMGN1QOLmMHFDI31DsCJTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [KPI_DATE = _t, REC_DATE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"KPI_DATE", type date}, {"REC_DATE", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Days Diff", each [dts={[KPI_DATE],[REC_DATE]}, first=List.Min(dts), last=List.Max(dts), diff=Duration.Days(last-first)+1, all=List.Dates(first,diff,#duration(1,0,0,0)), weekdays=List.Select(all, each Date.DayOfWeek(_)<>Day.Saturday and Date.DayOfWeek(_)<>Day.Sunday), networkdays=List.Count(weekdays), plusMinus=if [KPI_DATE] > [REC_DATE] then -1*networkdays else networkdays ][plusMinus], Int64.Type) in #"Added Custom"Your accepted answer from MFelix seems to give an incorrect result for the dates
7Jan 2026 to 15 Dec 2025
I think it should be -18 but accepted answer from MFelix returns -16 if I implemented it correctly.
My Implementation:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31TcyMDJT0lEy0reAMGN1QOLmMHFDI31DsCJTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [KPI_DATE = _t, REC_DATE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"KPI_DATE", type date}, {"REC_DATE", type date}}), #"Added Custom" = Table.AddColumn( #"Changed Type", "Days Diff", each try List.Count( List.Select( List.Dates([KPI_DATE], Duration.TotalDays([REC_DATE]-[KPI_DATE]) + 1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_)))) otherwise - List.Count( List.Select( List.Dates( [KPI_DATE], Duration.TotalDays([KPI_DATE]-[REC_DATE]) -1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))))) in #"Added Custom"
