Durchlaufzeit mit PowerQuery

[suse_bo]

Hallo zusammen,

ich brüte schon seit Tagen an einer etwas kniffligen Sache in PowerQuery und komme nicht weiter: 

Ich habe 2 TimeStamps und möchte die Differenz errechnen, muss aber noch folgende Bedingungen berücksichtigen:

TimeStamp1: DateCreated (ist ein beliebiger Zeitpunkt)
TimeStame2: DateOffering (ist immer ein Werktag, innerhalb der Dienstzeiten)

Bedingungen für DateCreated:
* Wochenende soll nicht berücksichtigt werden (quasi "herausgerechnet"). Beispiel: Liegt das DateCreated an einem Wochenende oder Freitag nach Dienstende (15.02.2020  00:12:57), möchte ich erst ab Montag 7:00 berechnen.
* Das Zeitfenster 18:00 - 7:00 soll nicht berücksichtigt werden. Beispiel: Liegt das DateCreated außerhalb der Dienstzeiten (11.02.2020  00:05:20), möchte ich erst ab 7:00 berechnen.

Hat jemand eine Idee?

Danke euch vorab!

[WillWissen]

Hi,

stell bitte eine Beispieldatei (Aufbau wie das Original) vor und trage händisch ein paar Wunschergebnisse ein. So geht's: https://www.clever-excel-forum.de/Thread...ng-stellen

[suse_bo]

eine Excel-Tabelle mit Beispieldatensätzen habe ich beigefügt.

[Sigi.21]

Hallo Suse_bo,

das ließe sich mit VBA leicht lösen.
Kommt für dich eine VBA-Funktion in Frage? Darfst du das einsetzen?

Sigi

[ruppy]

Hey suse_bo,

schau mal ob meine angehängte Lösung hinhaut.

Hier auch der M-Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="MailIn"]}[Content],
    #"Datentypen anpassen" = Table.TransformColumnTypes(Quelle,{{"ID", Int64.Type}, {"TalkType", type text}, {"TimeStampCreate", type datetime}, {"TimeStampOffering", type datetime}}),
    #"Neuer TimeStamp" = Table.AddColumn(#"Datentypen anpassen", "NewCreateTimeStamp", each if Date.DayOfWeek([TimeStampCreate]) = 5
then DateTime.FromText(
    Text.Combine(
        {
        Date.ToText(
            Date.AddDays(
                DateTime.Date([TimeStampCreate]),2))," 07:00:00"}))
else if Date.DayOfWeek([TimeStampCreate]) = 6
then DateTime.FromText(
    Text.Combine(
        {
        Date.ToText(
            Date.AddDays(
                DateTime.Date([TimeStampCreate]),1))," 07:00:00"}))
else [TimeStampCreate]),
    #"Neuen TimeStamp formatieren" = Table.TransformColumnTypes(#"Neuer TimeStamp",{{"NewCreateTimeStamp", type datetime}}),
    #"Finaler TimeStamp" = Table.AddColumn(#"Neuen TimeStamp formatieren", "Finaler TimeStamp", each if
Time.From([NewCreateTimeStamp]) >=
Time.FromText("18:00:00pm")
then
DateTime.FromText(
Text.Combine(
    {
Date.ToText(
Date.AddDays(
    DateTime.Date([NewCreateTimeStamp]),1))," 07:00:00"}))
else if
Time.From([NewCreateTimeStamp]) <=
Time.FromText("07:00:00am")
then
DateTime.FromText(
Text.Combine(
    {
Date.ToText(
DateTime.Date([NewCreateTimeStamp]))," 07:00:00"}))
else [NewCreateTimeStamp]),
    #"NewCreateTimeStamp löschen" = Table.RemoveColumns(#"Finaler TimeStamp",{"NewCreateTimeStamp"}),
    #"Finalen TimeStamp formatieren" = Table.TransformColumnTypes(#"NewCreateTimeStamp löschen",{{"Finaler TimeStamp", type datetime}}),
    #"Differenz berechnen" = Table.AddColumn(#"Finalen TimeStamp formatieren", "Differenz", each [TimeStampOffering]-[Finaler TimeStamp]),
    #"Differenz formatieren" = Table.TransformColumnTypes(#"Differenz berechnen",{{"Differenz", type duration}}),
    #"Wochentag ""Create"" zur Kontrolle" = Table.AddColumn(#"Differenz formatieren", "Name des Tags", each Date.DayOfWeekName([TimeStampCreate]), type text)
in
    #"Wochentag ""Create"" zur Kontrolle"



Habe es aber nicht wirklich kontrolliert.

#### Das fett markierte ist in der angehängten Datei gerade vertauscht und müsste angepasst werden!

BG ruppy

[suse_bo]

Wow! Krass! Wie kommt ihr nur immer so schnell zu so tollen Lösungen!!!

Jetzt da ich mich mit der Lösung beschäftige, fällt mir natürlich noch ein weiterer kniffliger Punkt ein:

Wenn zwischen dem "neuen" Startzeitpunkt und dem Entpunkt nun noch ein weiterer Tag oder ein Wochenende liegt, dann klappt das mit der Differenz nicht mehr.
Hier gelten ja dann auch die Bedingungen von oben.

Beispiel:
 Start: 13.02.2020 16.44 Uhr, Ende 17.02.2020 16.12 Uhr --> Durchlaufzeit sind hier nicht 3d 23.27.44 , sondern nur 21.18

Wie würdet ihr das machen? Von der Differenz entsprechend Subtrahieren?

[Sigi.21]

Hallo Suse_bo,

Zitat:  "   Start: 13.02.2020 16.44 Uhr, Ende 17.02.2020 16.12 Uhr --> Durchlaufzeit sind hier nicht 3d 23.27.44 , sondern nur 21.18  "
Ich komme da auf 21:28  (unterstellt: Mo-Fr, tgl. 7-18h, ). Wie schon erwähnt, mit VBA-Fkt. kein Problem. Kommt das für dich in Frage?
In deinen Grunddaten sind im Zeitstempel auch die Sekunden angegeben. Brauchst du das sekundengenau? Oder reicht minutengenau? Spielen Feiertage eine Rolle?

Gruß
Sigi

[suse_bo]

Du hast recht, ich habe mich verrechnet.

Feiertage hätte ich jetzt vernachlässigt, das ist nicht relevant.
Minuten genügen.

Wenn mir VBA zu einer soliden Lösung verhilft, dann gerne auch das. Ich lerne ja immer gern dazu :)

[Sigi.21]

Hallo Suse_bo,

siehe Mappe!

Gruß
Sigi

[suse_bo]

Liebe Excel-Profis :)

Die letzte Excel aus diesem Thread ist seither täglich bei uns im Einsatz und erleichert unser Arbeiten wirklich ungemein!
Lieben Dank nocheinmal dafür.

So tollkühn ich ich anfangs behauptete, dass Feiertage keine Rolle spielen sollen,
so beschämt muss ich die Aussage revidieren.

Wie würde man die Feiertagslogik hier in das VBA mit einbauen können? Über eine Hilfstabelle?

Herzliche Grüße!