Why my tuesdays are not included in my formula ?
Hello guys 🙂
I am completely new to Excel and Power Query. I'm trying to automate a recurring task at work. Essentially, I have orders and the day they are dispatched (column: DispatchDate). I add a column on my end called Out of Delay Date, which takes into account 4 working days for the deadline (I don't count weekends, but I do count everything else, including holidays). The formula works well EXCEPT when the DispatchDate is a Tuesday, and then it goes awry, and the Out of Delay Date falls on the following Sunday, whereas if we consider 4 working days, it should always be the following Friday.
Within the 4 working days, the DispatchDate is included. Example: DispatchDate = Tuesday (1), Wednesday (2), Thursday (3), Friday (4)
Why do I have this issue only with Tuesdays? Why does the logic work everywhere else except for this particular day?
Here is my entire code :
​
​
let
Source = Excel.Workbook(File.Contents("C:\\Users\\AU00126T\\Downloads\\UD.xlsx"), null, true),
RecordSet\_Sheet = Source{\[Item="RecordSet",Kind="Sheet"\]}\[Data\],
\#"En-têtes promus" = Table.PromoteHeaders(RecordSet\_Sheet, \[PromoteAllScalars=true\]),
\#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"WOGroupNo", type text}, {"WorkOrder", type text}, {"SerialNo", type text}, {"OnHold", type text}, {"Product", Int64.Type}, {"ProductDesc", type text}, {"Workflow", type text}, {"Spec", type text}, {"MoveStatus", type text}, {"ModelFactory", type text}, {"ProduceFactory", type text}, {"DispatchDate", type datetime}, {"DueDate", type datetime}, {"SoldToBP", type text}, {"SoldToBPName", type text}, {"PatientName", type text}, {"ShipToBP", type text}, {"ERPPriority", type text}, {"ProductionPriority", type text}, {"Resource", type text}, {"LineName", type text}}),
\#"Lignes filtrées" = Table.SelectRows(#"Type modifié", each not Text.StartsWith(\[WOGroupNo\], "S")),
\#"Lignes filtrées1" = Table.SelectRows(#"Lignes filtrées", each (\[Workflow\] = "ITE\_REPAIR\_OPF")),
\#"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées1",{"LineName", "Resource", "ProductionPriority", "ERPPriority", "ShipToBP", "PatientName", "SoldToBPName", "SoldToBP", "DueDate"}),
\#"Lignes triées" = Table.Sort(#"Colonnes supprimées",{{"DispatchDate", Order.Ascending}}),
\#"Lignes filtrées2" = Table.SelectRows(#"Lignes triées", each (\[Spec\] <> "CSO\_AFFILATE PENDING")),
\#"Type modifié1" = Table.TransformColumnTypes(#"Lignes filtrées2",{{"DispatchDate", type date}}),
\#"Filtrer DispatchDate" = Table.SelectRows(#"Type modifié1", each \[DispatchDate\] <= Date.From(DateTime.LocalNow() - #duration(1, 0, 0, 0))),
\#"Personnalisée ajoutée" = Table.AddColumn(#"Filtrer DispatchDate", "Out of delay date", each let
AddBusinessDays = (startDate as date, numDays as number) as date =>
let
weekdayList = {1, 2, 3, 4, 5},
daysAdded = List.Generate(() => \[Count=0, Date=startDate\],
each \[Count\] < numDays,
each let
nextDay = Date.AddDays(\[Date\], 1),
isWeekday = List.Contains(weekdayList, Date.DayOfWeek(nextDay, Day.Monday) + 1)
in \[Count=if isWeekday then \[Count\]+1 else \[Count\],
Date=nextDay\]),
lastDay = List.Last(daysAdded)\[Date\]
in
lastDay
in
AddBusinessDays(\[DispatchDate\], 4)), // Modification ici pour prendre en compte 4 jours ouvrés
\#"Lignes triées1" = Table.Sort(#"Personnalisée ajoutée",{{"Out of delay date", Order.Ascending}}),
\#"Type modifié2" = Table.TransformColumnTypes(#"Lignes triées1",{{"Out of delay date", type date}}),
\#"Personnalisée ajoutée1" = Table.AddColumn(#"Type modifié2", "Updates", each ""),
\#"Type modifié3" = Table.TransformColumnTypes(#"Personnalisée ajoutée1",{{"Updates", type date}})
in
\#"Type modifié3"
​
​
​
I'm sorry if I'm retarded, I just don't understand why tuesday is an issue ?
