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


Share on facebook
Share on twitter
Share on linkedin

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.

Share on facebook
Share on twitter
Share on linkedin

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

two level data validation drop down excel

Two-level Data Validation [Excel Trick]

Ever wanted to create a two-level data validation list? You can use this simple trick to make two-level or cascading drop-down validation lists in Excel. You need some data, a pivot table and simple IF formulas to get this. Read on to understand the process and create your own two-level drop down lists in Excel.

29 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
    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"})
    #"Replaced Value"

    2nd Query
    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"})
    #"Expanded All"

  5. GraH says:

    Or with some named ranges in Excel

    CallTime:= MOD(Calls!$B7,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:

      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);

  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


  14. David Wasserman says:

    I introduced a helper column in the Table with the following formula:

    =OR(VLOOKUP(TEXT([@[Call time]],"dddd"),working.hours,2,0)>TIMEVALUE(TEXT([@[Call time]],"hh:mm")),VLOOKUP(TEXT([@[Call time]],"dddd"),working.hours,3,0)<TIMEVALUE(TEXT([@[Call time]],"hh:mm")))

  15. Lalith says:

    Dear Chandoo, Can you pls explain the inclusion of countifs formula in sumproduct. I just can't understand if it makes a 5x1166 array or a 1166x5 array. Thanks in Advance!

  16. Eric says:

    I used DAX, so my formula is stupid simple.

    Total Calls:=COUNTROWS('calls')

    As far as columns go, I wrote the following formula:

    =IF(calls[Day Name]="Sunday","Closed",IF(OR(RELATED(hours[Start])>calls[Hour],RELATED(hours[End])<calls[Hour]),"Closed","Open"))

    Build a Pivot with this, and slice by Open/Closed and day of the week and you're done.

    Total Calls Column Labels
    Row Labels Closed Open Grand Total
    Sunday 158 158
    Monday 83 99 182
    Tuesday 104 107 211
    Wednesday 77 88 165
    Thursday 48 110 158
    Friday 79 66 145
    Saturday 110 37 147
    Grand Total 659 507 1,166



    • Eric says:

      This exercise would have been a little more drawn out if any of the closing times were in the middle of the hour; say for example 16:30. 🙂

      Still not too bad though. 🙂

      • Eric says:

        Sorry. I think the column formula was a little hard to read.

        =IF(calls[Day Name]="Sunday","Closed",

  17. Kim says:

    Better late than never, right? My answer is every different than all the others! This formula was in my "Outside office hours?" column:

    =OR(INDEX(working.hours[End],WEEKDAY([@[Call time]],2))<(B7-TRUNC(B7)),(B7-TRUNC(B7))<(8/24))

    I used 8/24 to represent the open time of 8am and was able to automatically weed out Sundays because the end-time was blank. I would have used a similar index if the start times varied. If Sunday weren't listed, I would have used iferror to force a 7 weekday answer to true.

    For the total I used:
    =COUNTIF(calls[Outside office hours?],TRUE())

    • Kim says:

      Realizing now what the mod() functions were doing above, very nice! I will put that in my back pocket for later, much cleaner than #-trunc()!

  18. sam says:

    =SUMPRODUCT(--ISERROR(MATCH(WEEKDAY(calls[Call time],2)+calls[Call time]-INT(calls[Call time]),(ROW(working.hours[Day])-6+(working.hours[End])))))

    gives 134

  19. Sushmita Bhar says:

    for identify call receive outside working hour:
    =IF(OR(TIME(HOUR([@[Call time]]),MINUTE([@[Call time]]),SECOND([@[Call time]]))VLOOKUP(INDEX(working.hours[Day],WEEKDAY([@[Call time]],2)),working.hours,3,FALSE)),"yes","no")

    for total number of call receive outside working hour:
    =COUNTIF(calls[outside office hr],"yes")

  20. Himanshu Jain says:

    for weekday:=TEXT(calls[@[Call time]],"dddd")
    for call time: =TEXT(calls[@[Call time]],"hh:mm")

    to check if call were made within the business hour or not:

Leave a Reply