cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

calculate time

Hi,

 

I am trying to calculate the time between two timestamps witihout weekends and holidays but dont know how. Also the time should only calculate working hours from 08:00 - 17:00. The time that has to be calculated is the begin and end time for incoming tickets for our service desk.

 

I have marked the columns of which the calculations has to be done:

Tijd berekenen tussen start en eindtijd1.png

 

3 REPLIES 3
Highlighted
Helper I
Helper I

Re: calculate time

Hi @shahbaaz ,

 

i've dealed with a similar task in the past and realised it with a Date-Table created in PowerQuery wich contains a Row that defines a Workday. I also used a Webquery from a website where i can get the national holidays from. To calculate the Net-WorkingDays and Hours i did a step by step PowerQuery wich is working with a function that creates a list of dates between two dates. It's hard to describe but here are the Queries:

 

Function that creates the list of Dates between 2 Dates:

let
    Func = (startDate as date, numberOfMonthlyRates as number) => 
        List.Generate( 
            () => [currentDate = startDate , currentMonth = 0],
            (_) => _[currentMonth] < numberOfMonthlyRates,
            (_) => [currentMonth =  [currentMonth] + 1, currentDate = Date.AddDays([currentDate],1)],
            (_) => _)
in
    Func

CalendarTabel with Dimensions and Column containing the WorkDays:

let
    Source = List.Dates,
    InvokedSource = Source(#date(2017,1,1),Duration.Days(DateTime.Date(Date.EndOfYear(DateTime.FixedLocalNow()))-#date(2017,1,1)),#duration(1,0,0,0)),
    #"In Tabelle konvertiert" = Table.FromList(InvokedSource, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Umbenannte Spalten" = Table.RenameColumns(#"In Tabelle konvertiert",{{"Column1", "Datum"}}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Datum", type date}}),
    #"Jahr eingefügt" = Table.AddColumn(#"Geänderter Typ", "Year", each Date.Year([Datum]), Int64.Type),
    #"Monat eingefügt" = Table.AddColumn(#"Jahr eingefügt", "MonthID", each Date.Month([Datum]), Int64.Type),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Monat eingefügt", "Month", each Date.ToText([Datum],"MMM")),
    #"Quartal eingefügt" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "QuartalID", each Date.QuarterOfYear([Datum]), Int64.Type),
    #"Wochentag eingefügt" = Table.AddColumn(#"Quartal eingefügt", "WeekdayID", each Date.DayOfWeek([Datum])+1, Int64.Type),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Wochentag eingefügt", "Weekday", each Date.ToText([Datum],"ddd")),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "KWID Teil1", each Number.From([Datum])-[WeekdayID]+11),
    #"Hinzugefügte benutzerdefinierte Spalte3" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte2", "KWID Teil2", each Number.From(#date(Date.Year(Date.From(Number.From([Datum])+4-[WeekdayID])),1,1))),
    #"Subtraktion eingefügt" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte3", "Subtraktion", each [KWID Teil1] - [KWID Teil2], type number),
    #"Division eingefügt" = Table.AddColumn(#"Subtraktion eingefügt", "Division", each [Subtraktion] / 7, type number),
    #"Hinzugefügte benutzerdefinierte Spalte4" = Table.AddColumn(#"Division eingefügt", "KWID", each [Division]-Number.Mod([Division],1), type text),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte4",{"KWID Teil1", "KWID Teil2", "Subtraktion", "Division"}),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Entfernte Spalten",{{"KWID", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte5" = Table.AddColumn(#"Geänderter Typ1", "KW", each "KW "&[KWID]),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte5",{{"QuartalID", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte6" = Table.AddColumn(#"Geänderter Typ2", "Quartal", each "Q"&[QuartalID]),
    #"Geänderter Typ3" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte6",{{"Year", type text}, {"QuartalID", Int64.Type}, {"Weekday", type text}, {"KW", type text}, {"Quartal", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte7" = Table.AddColumn(#"Geänderter Typ3", "KWYear", each [KWID]&[Year]),
    #"Geänderter Typ4" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte7",{{"KWID", Int64.Type}, {"KWYear", type text}, {"Month", type text}}),
    #"Zusammenführte Abfragen" = Table.NestedJoin(#"Geänderter Typ4",{"Datum"},#"Feiertage 2018 im Bundesland Hessen:",{"Datum"},"Feiertage 2018 im Bundesland Hessen:",JoinKind.LeftOuter),
    #"Erweiterte Feiertage 2018 im Bundesland Hessen:" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "Feiertage 2018 im Bundesland Hessen:", {"Datum"}, {"Datum.1"}),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Erweiterte Feiertage 2018 im Bundesland Hessen:",{{"Datum.1", "Dat_Feiertag"}}),
    #"Hinzugefügte benutzerdefinierte Spalte8" = Table.AddColumn(#"Umbenannte Spalten1", "isWorkDay", each if [WeekdayID]<6 and [Dat_Feiertag]=null then 1 else 0)
in
    #"Hinzugefügte benutzerdefinierte Spalte8"

WebQuery for Holidays:

let
    Quelle = Web.Page(Web.Contents("https://www.schulferien.org/Feiertage/Feiertage_Hessen.html")),
    Data3 = Quelle{3}[Data],
    #"Geänderter Typ1" = Table.TransformColumnTypes(Data3,{{"Header", type text}, {"Datum", type date}, {"WochentagTag", type text}, {"Name", type text}, {"KW", type text}, {"Urlaub?", type text}, {"Column7", type text}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ1", each ([KW] <> null))
in
    #"Gefilterte Zeilen"

 

Result, depends on Excel-Based Example Data

t
    Quelle = Excel.Workbook(File.Contents("C:\Users\schaeferb\Desktop\TestZeit.xlsx"), null, true),
    Tabelle1_Table = Quelle{[Item="Tabelle1",Kind="Table"]}[Data],
    #"Geänderter Typ" = Table.TransformColumnTypes(Tabelle1_Table,{{"Erfassung", type datetime}, {"Lösung", type datetime}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "DauerErfassung", each if DateTime.Date([#"Erfassung"])=DateTime.Date([Lösung]) then [Lösung]-[#"Erfassung"] else if DateTime.Time([#"Erfassung"])<=DateTime.Time(#datetime(Date.Year([#"Erfassung"]),Date.Month([#"Erfassung"]),Date.Day([#"Erfassung"]),8,0,0)) then 8/24 else DateTime.Time(#datetime(Date.Year([#"Erfassung"]),Date.Month([#"Erfassung"]),Date.Day([#"Erfassung"]),17,0,0))-DateTime.Time([#"Erfassung"])),
    #"Subtraktion des eingefügten Datums" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Subtraktion", each Duration.Days([Lösung] - [#"Erfassung"])+1, Int64.Type),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Subtraktion des eingefügten Datums",{{"Subtraktion", "Tage"}}),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Umbenannte Spalten", "DauerLösung", each if DateTime.Date([#"Erfassung"])=DateTime.Date([Lösung]) then 0 else if DateTime.Time([Lösung])>=DateTime.Time(#datetime(Date.Year([Lösung]),Date.Month([Lösung]),Date.Day([Lösung]),17,0,0)) then 8/24 else DateTime.Time([Lösung])-DateTime.Time(#datetime(Date.Year([Lösung]),Date.Month([Lösung]),Date.Day([Lösung]),8,0,0))),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "DauerZwischen", each if [Tage]>2 then ([Tage]-2)*8/24 else 0),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte2",{{"DauerErfassung", type duration}, {"DauerLösung", type duration}, {"DauerZwischen", type duration}}),
    #"Summe eingefügt" = Table.AddColumn(#"Geänderter Typ1", "Addition", each List.Sum({[DauerErfassung], [DauerLösung], [DauerZwischen]}), type duration),
    #"Datum eingefügt" = Table.AddColumn(#"Summe eingefügt", "Datum", each DateTime.Date([#"Erfassung"]), type date),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Datum eingefügt",{{"Datum", "Dat_Erfasst"}}),
    #"Aufgerufene benutzerdefinierte Funktion" = Table.AddColumn(#"Umbenannte Spalten1", "Dates", each ListDates([Dat_Erfasst], [Tage])),
    #"Erweiterte Dates" = Table.ExpandListColumn(#"Aufgerufene benutzerdefinierte Funktion", "Dates"),
    #"Erweiterte Dates1" = Table.ExpandRecordColumn(#"Erweiterte Dates", "Dates", {"currentDate"}, {"currentDate"}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte Dates1",{"DauerErfassung", "Tage", "DauerLösung", "DauerZwischen", "Lösung"}),
    #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Entfernte Spalten",{"currentDate", "Erfassung", "Dat_Erfasst", "Addition"}),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Neu angeordnete Spalten",{{"currentDate", type date}}),
    #"Zusammenführte Abfragen" = Table.NestedJoin(#"Geänderter Typ2",{"currentDate"},Kalender,{"Datum"},"Kalender",JoinKind.LeftOuter),
    #"Erweiterte Kalender" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "Kalender", {"isWorkDay"}, {"isWorkDay"}),
    #"Geänderter Typ3" = Table.TransformColumnTypes(#"Erweiterte Kalender",{{"isWorkDay", Int64.Type}, {"Addition", type number}}),
    #"Hinzugefügte benutzerdefinierte Spalte3" = Table.AddColumn(#"Geänderter Typ3", "NettoZeit", each if [isWorkDay]=0 then [Addition]-((1/24)*8) else [Addition]),
    #"Geänderter Typ4" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte3",{{"Addition", type duration}, {"NettoZeit", type duration}}),
    #"Gruppierte Zeilen" = Table.Group(#"Geänderter Typ4", {"Erfassung"}, {{"NettoZeit2", each List.Average([NettoZeit]), type duration}}),
    #"Geänderter Typ5" = Table.TransformColumnTypes(#"Gruppierte Zeilen",{{"NettoZeit2", type number}}),
    #"Multiplikation eingefügt" = Table.AddColumn(#"Geänderter Typ5", "Multiplikation", each [NettoZeit2] * 24, type number),
    #"Umbenannte Spalten2" = Table.RenameColumns(#"Multiplikation eingefügt",{{"Multiplikation", "Stunden"}}),
    #"Geänderter Typ6" = Table.TransformColumnTypes(#"Umbenannte Spalten2",{{"NettoZeit2", type duration}}),
    #"Hinzugefügte benutzerdefinierte Spalte4" = Table.AddColumn(#"Geänderter Typ6", "InTime", each if [Stunden]<=8 then true else false),
    #"Geänderter Typ7" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte4",{{"InTime", type logical}})
in
    #"Geänderter Typ7"

I'm only able to provide it in German but maybe you can get a clue on whats going on. This is the Result:

 

From this:

 

Raw Example DataRaw Example Data

 

 

 

 

 

 

 

To this:

 

Result.png

 

 

 

 

 

I hope it helps.

 

Regards

 

BjoernSchaefer

Highlighted
Helper I
Helper I

Re: calculate time

Hey, thanks for your response. Since I am new to Power BI, I can't really figure out what is going wrong. I tried copying and pasting your code within a new column which wasn't doing anything but besides giving error.

 

Regards.

 

Shahbaaz

Highlighted
Community Support
Community Support

Re: calculate time

@shahbaaz ,

 

Take a look at The Advanced Editor.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors