How many calls we got outside office hours? [Excel / Power Query homework]


Time for another Excel formula / Power Query challenge. This is based on a common business data analysis problem. Say you have two tables – calls log and office hours. Call log tells when each call is received. Office hours tell us working hours for seven days of the week. We want to know how many calls are outside office hours.

Count calls outside office hours - how to count or identify them? Business data analysis in Excel

Count calls outside business hours – problem

So you are up for the challenge eh? Just follow the below steps.

You have three problems to solve.

  1. Write a formula in calls table to identify if a call is outside office hours. Can be Yes/No or TRUE/FALSE
  2. Write a single formula outside the tables to count how many calls were made outside office hours. The answer should be 693. You can use helper columns (or the column in problem#1) if you want.
  3. Set up a Power Query to filter all outside office hours calls.

Successful in finding the answers? Please post them in comments.

If you have an answer, please post your formulas / VBA / M / spells in the comments area.

Want some clues? Try these – BETWEEN formula in Excel, Range lookup, Working hours problem

Of course there is a hidden answers worksheet in the download. So check that out if you feel stumped.

Want more problems?

Check out Excel homework and Excel challenges pages for some hard, interesting and fascinating problems to solve.

All the best. I am waiting for your answers.

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or may be join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

19 Responses to “How many calls we got outside office hours? [Excel / Power Query homework]”

  1. Fabio says:

    =IF(AND(MOD([@[Call time]];1)>=INDEX(working.hours[Start];WEEKDAY([@[Call time]];2));MOD([@[Call time]];1)<=INDEX(working.hours[End];WEEKDAY([@[Call time]];2)));"yes";"no")

  2. PR says:

    I added one helper column labeled "Weekday" in between "Day" and "Start" columns to assign a number (1 thru 7) for each weekday (Monday=1 thru Sunday=7).

    1. =IF([@Time]=MEDIAN([@Time],VLOOKUP(WEEKDAY([@[Call time]],2),working.hours[[Weekday]:[End]],2,FALSE),VLOOKUP(WEEKDAY([@[Call time]],2),working.hours[[Weekday]:[End]],3,FALSE)),"No","Yes")

    2.=COUNTIF(calls[After hours?],"Yes")

    • PR says:

      Updated Solution without helper column:

      =IF([@Time]=MEDIAN([@Time],VLOOKUP(TEXT([@[Call time]],"dddd"),working.hours[#All],2,FALSE),VLOOKUP(TEXT([@[Call time]],"dddd"),working.hours[#All],3,FALSE)),"No","Yes")

  3. lee hibbert says:

    Because I don't like big formulas I created 5 helper columns
    Col C Weekday =WEEKDAY([@[Call time]])
    Col D Time of Call =[@[Call time]]-INT([@[Call time]])
    Col E Start Time for Day =INDEX(working.hours[Start],MATCH([@WeekDay],working.hours[WeekDay],0))
    Col F End Time for Day =INDEX(working.hours[End],MATCH([@WeekDay],working.hours[WeekDay],0))
    Col O Just number the Days

    Col G =IF([@Time]=MEDIAN(calls[@[Time]:[Day_End]]),"","Out of Hours")

    =COUNTIF(calls[Out of Hours],"Out of Hours") gives me the correct 693

  4. GraH says:

    Answered all with 1 PQ solution, using only the UI.
    Loaded both tables...
    1st query
    let
    Source = Excel.CurrentWorkbook(){[Name="working.hours"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}, {"Start", type number}, {"End", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"Start", type time}, {"End", type time}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Day", "Day Name"}, {"Index", "Day of Week"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,#time(23, 59, 59),Replacer.ReplaceValue,{"Start", "End"})
    in
    #"Replaced Value"

    2nd Query
    let
    Source = Excel.CurrentWorkbook(){[Name="calls"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call time", type datetime}}),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Call time]), type text),
    #"Inserted Time" = Table.AddColumn(#"Inserted Day Name", "Time", each DateTime.Time([Call time]), type time),
    #"Appended Query" = Table.Combine({#"Inserted Time", #"working hours"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Day Name", Order.Ascending}, {"Day of Week", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Start", "End", "Day of Week"}),
    #"Added Conditional Column" = Table.AddColumn(#"Filled Down", "Custom", each if [Time] > [End] then "Outside" else if [Time] < [Start] then "Outside" else "Inside"),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Call time] null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom"}, {{"Count", each Table.RowCount(_), type number}, {"All", each _, type table [Call time=datetime, Day Name=text, Time=time, Start=time, End=time, Day of Week=number, Custom=text]}}),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([Custom] = "Outside")),
    #"Expanded All" = Table.ExpandTableColumn(#"Filtered Rows1", "All", {"Call time"}, {"Call time"})
    in
    #"Expanded All"

  5. GraH says:

    Or with some named ranges in Excel

    CallTime:= MOD(Calls!$B7,1)
    CallDOW:=WEEKDAY(Calls!$B7,2)
    EndHr:=INDEX(working.hours[End],CallDOW)
    StartHr:=INDEX(working.hours[Start],CallDOW)

    [C7]=(OR(CallTime>=EndHr;CallTime<=StartHr)*1)

    Total via auto sum of table.
    Idem for filter.

  6. Jozef Melichar says:

    1. =IFERROR(IF(
    AND(MOD([@[Call time]];1)>=INDEX(working.hours[Start];MATCH(TEXT([@[Call time]];"dddd[$-409]");working.hours[Day];0));
    MOD([@[Call time]];1) filter False ->remove Is in Column

    • Jozef Melichar says:

      1. =IFERROR(IF(
      AND(MOD([@[Call time]];1)>=INDEX(working.hours[Start];MATCH(TEXT([@[Call time]];"dddd[$-409]");working.hours[Day];0));
      MOD([@[Call time]];1)filter False and remove Is in column

    • Jozef Melichar says:

      IFERROR(IF(
      AND(MOD([@[Call time]];1)>=INDEX(working.hours[Start];MATCH(TEXT([@[Call time]];"dddd[$-409]");working.hours[Day];0));
      MOD([@[Call time]];1)<=INDEX(working.hours[End];MATCH(TEXT([@[Call time]];"dddd[$-409]");working.hours[Day];0)));TRUE;FALSE);
      FALSE)

  7. Terry says:

    Hi all,

    A few people have posted the formula containing MOD function which is quite elegant in my opinion. I am not good at using MOD so I used the following formula instead for the same purpose:

    =IF(AND((HOUR([@[Call time]])+MINUTE([@[Call time]])/60)>HOUR(INDEX(working.hours[Start],WEEKDAY([@[Call time]],2))),(HOUR([@[Call time]])+MINUTE([@[Call time]])/60)<HOUR(INDEX(working.hours[End],WEEKDAY([@[Call time]],2)))),0,1)

    Sums up the result and I got 693 which seems correct.

    I also managed to use PQ to solve the problem. In brief:
    1) add both tables to power query editor;
    2) in the calls table I duplicated the column and transform one column to 'day of the week' and the other to 'call time';
    3) I merged the calls table with the working hours table and expanded the start and end column from the working hours table;
    4) I added three helper columns (call them a, b and c) to do the following calculations:
    a. Call Time - Start Time
    b. End Time - Call Time
    c. results from a x results from b
    5) replace null in column c with -1, then filter column c with only negative values, and whatever is shown should be calls outside working hours. Have to admit, the table does not look very pretty. Took me roughly 20 minutes to work it out 🙂

    So my question would be, how to write a single formula without using a helper column to find out how many calls are outside working hours. I guess it will take a mind on a higher dimension to work it out...

  8. Stéphane says:

    In "working.hours" table

    in french
    =SOMMEPROD((TEXTE(calls[Call time];"jjjj")=[@Day])*((MOD(calls[Call time];1)[@End])))

    =SUMPRODUCT((TEXT(calls[Call time];"dddd")=[@Day])*((MOD(calls[Call time];1)[@End])))

    Monday 95
    Tuesday 111
    Wednesday 79
    Thursday 51
    Friday 84
    Saturday 115
    Sunday 158
    TOTAL 693

  9. Stéphane says:

    =SOMMEPROD((TEXTE(calls[Call time];"jjjj")=[@Day])*((MOD(calls[Call time];1)[@End])))

  10. Stéphane says:

    =SOMMEPROD((TEXTE(calls[Call time];"jjjj")=[@Day])*((MOD(calls[Call time];1)>[@End])+(MOD(calls[Call time];1)<[@Start])))

  11. Chihiro says:

    My preferred PQ solution.

    Add Wkday column to Call Time:
    =Date.DayOfWeekName([Call time])

    Merge "working hours" using Wkday & Day as key.

    Add custom:
    if [working hours][Start]{0} = null then false else [working hours][Start]{0} = Time.From([Call time])

    Filter for FALSE. Done.

    • Chihiro says:

      Last Custom got truncated. Should be...
      =if [working hours][Start]{0} = null then false else [working hours][Start]{0} = Time.From([Call time])

      • Chihiro says:

        .... Hmm, can't post the exact formula for some reason.

        It's just simple check to see if start less than or equal to Call time AND end greater than or equal to Call time.

  12. QPRO says:

    SIMPLE AND STRAIGHTFORWARD is the VLOOKUP (approximate match) FUNCTION, which is designed for just this type of problem with multiple "in between" choices.

    Each time change point in the week is listed in ascending order. After 0 for Sundays, they are given for each following weekday (“wday” from 1 to 6) as:
    = wday + INDEX(working.hours,wday,2) for the start time, and
    = wday + INDEX(working.hours,wday,3) for the end time of the day.
    These form the first column for the VLOOKUP Table: 0 (Sunday Start from midnight); 1.3333 (Monday Start); 1.75 (Monday End); 2.3333 (Tuesday Start); 2.75 (Tuesday End); and so on up to 6.3333 (Saturday Start); 6.45167 (Saturday End).

    The second column of the VLOOKUP table alternates between the values for Outside or for Inside business hours, for example: 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1, where the 1 represents outside business hours, and the 0 is during the working day.

    THE HEART OF THE MATTER is the function =MOD(CallTime, 7) which takes each item from the calls list and produces an integer part from 0 to 6 for the day of the week, and a decimal part for the time on the day. Adjust this formula with a minus one, to give =MOD(CallTime -1, 7) to make the integer results run from Sunday=0, Monday=1, up to Saturday=6, plus the time of each particular call.

    Copy the following formula down from C7 to C1172

    =VLOOKUP(MOD(B7 -1, 7),vlTable,2,TRUE)
    This TRUE defines the approximate matches function, and may be omitted.

    Then a SUM of column C gives the number of calls outside business hours.
    We could, instead, make the outside values TRUE, and the inside values FALSE, and count the “TRUE” values to see how many calls fell outside business hours.
    Another eye-catching possibility is “Out of hours” for outside, and = “” for inside business hours.

    BUG, FEATURE OR PROBLEM? The Sort Options were greyed out when column B was included. However the Sort worked after copying B7:C1173 to a new sheet,.

    MORE DETAILED ANALYSIS can result from use of the following two-column vlTable, but just remember that “After hours Sunday” includes up to 8 AM Monday morning.

    0, After hours Sunday;
    1.3333, Monday;
    1.75, After hours Monday;
    2.3333, Tuesday;
    2.75, After hours Tuesday;
    3.3333, Wednesday;
    3.75, After hours Wednesday;
    4.3333, Thursday;
    4.875, After hours Thursday;
    5.3333, Friday;
    5.6667, After hours Friday;
    6.3333, Saturday;
    6.5417, After hours Saturday;

  13. Eduardo says:

    My answer

    =SI([@Hora]=MEDIANA(INDIRECTO([@Día]),[@Hora]),"SI","NO")

Leave a Reply


« »