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 business hours – problem
So you are up for the challenge eh? Just follow the below steps.
- Download the problem data set.
- There are two tables. calls & working.hours.
You have three problems to solve.
- Write a formula in calls table to identify if a call is outside office hours. Can be Yes/No or TRUE/FALSE
- 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.
- 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.
30 Responses to “How many calls we got outside office hours? [Excel / Power Query homework]”
=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")
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")
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")
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
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"
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.
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
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
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)
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...
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
=SOMMEPROD((TEXTE(calls[Call time];"jjjj")=[@Day])*((MOD(calls[Call time];1)[@End])))
=SOMMEPROD((TEXTE(calls[Call time];"jjjj")=[@Day])*((MOD(calls[Call time];1)>[@End])+(MOD(calls[Call time];1)<[@Start])))
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.
Last Custom got truncated. Should be...
=if [working hours][Start]{0} = null then false else [working hours][Start]{0} = Time.From([Call time])
.... 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.
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;
Wow... the MOD(B7 -1, 7) idea and vlookup table are genius... 🙂
My answer
=SI([@Hora]=MEDIANA(INDIRECTO([@Día]),[@Hora]),"SI","NO")
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")))
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!
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
Best,
Eric~
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. 🙂
Sorry. I think the column formula was a little hard to read.
=IF(calls[Day Name]="Sunday","Closed",
IF(OR(RELATED(hours[Start])>calls[Hour],RELATED(hours[End])<calls[Hour]),"Closed","Open"))
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())
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()!
=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
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")
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:
=IF(OR(AND(OR(C12="Monday",C12="Tuesday",C12="Wednesday"),D12>"08:00",D12"08:00",D12"08:00",D12"08:00",D12<"13:00")),"yes","no")
Hi,
I created a helper column in row E with each day numbered from 1 to 7.
Then apply the formula below by creating an "Outside business hours" header in row D.
=IF(AND(TIME(HOUR(B7),MINUTE(B7),SECOND(B7))>INDEX(working.hours[Start],MATCH(WEEKDAY(B7,2),$E$7:$E$13,0)),TIME(HOUR(B7),MINUTE(B7),SECOND(B7))0,INDEX(working.hours[End],MATCH(WEEKDAY(B7,2),$E$7:$E$13,0))>0),"No","Yes")
Voila!