Negative difference in Days

[Rich_Wyeth]

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.

[ronrsnfld]

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"

 

[ronrsnfld]

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"

[MFelix · Accepted solution]

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(_))))