Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Why my tuesday are not counted in this code ? (only tuesday)

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" 
 
&#x200B;
 
&#x200B;
 
&#x200B;
 
I'm sorry if I'm retarded, I just don't understand why tuesday is an issue ?
Need Help

1 Reply

  • slorin's avatar
    slorin
    Icon for Super User rankSuper User
    2 years ago

    Bonjour
    Vous semblez parler français, ce sera plus simple pour moi

    pour AddBusinessDays j'utiliserai plutôt une fonction récursive

     

    (startDate as date, numDays as number) as date =>
    if numDays= 0 and Date.DayOfWeek(startDate, Day.Monday)<=4 then startDate 
    else @AddBusinessDays(Date.AddDays(startDate,1), if Date.DayOfWeek(startDate, Day.Monday)<=4 then numDays-1 else numDays)

    si numDays = 0 et qu'on est en semaine, on renvoie la date

    sinon, on exécute la même fonction le lendemain en retirant 1 à numDays si on est en semaine

     

    Stéphane