Multiple IF function to trigger yes or no response

C

cmacnab

Board Regular
Joined
Jun 24, 2013
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
Hi all, I'm trying to do something fairly simple in logic but difficult in Excel.

I have data that has dates, times & job codes. I want to trigger a yes or no response to the question does date XYZ have the required job code. I want to create simple table with dates in column A and job codes in the B, C & D columns that will tell me YES or NO if the job code is present on a certain day for AM or PM time slots. Based on the data in the mini sheet, I want to build a table that will tell me if I had the BUSSER job code in the AM (prior to 5pm) or the PM (after 5pm) time slot.

PP25-2021 Reuben's (2020-11-29 to 2021-12-12).xlsx
BCDFGH
1DateFirstLastIn TimeOut TimeRole
5Tue-30-Nov-21WaynePaul11:00AM 5:07PMBARTENDER
10Tue-30-Nov-21JustinBrassard11:00AM 2:23PMGREETER
14Tue-30-Nov-21ShishirShekh11:06AM 4:58PMBUSSER
15Tue-30-Nov-21TaliTreister11:30AM 6:47PMGREETER
27Tue-30-Nov-21AbigaelleVasseur4:25PM 10:04PMGREETER
32Tue-30-Nov-21StephanieCarter5:05PM 10:35PMBARTENDER
42Wed-1-Dec-21JustinBrassard11:00AM 5:00PMGREETER
45Wed-1-Dec-21DannyChin11:05AM 4:59PMBARTENDER
47Wed-1-Dec-21ShishirShekh11:18AM 4:58PMBUSSER
59Wed-1-Dec-21GenevieveTurgeon5:00PM 10:33PMBARTENDER
60Wed-1-Dec-21Daniel-DominikDumont5:00PM 11:15PMBUSSER
Punch Report


This is what the table would look like based on the data above:

PP25-2021 Reuben's (2020-11-29 to 2021-12-12).xlsx
ABCDEFG
1AMPM
2DateBUSSERGREETERBARTENDERBUSSERGREETERBARTENDER
311/30/21YESYESYESNOYESYES
412/01/21YESYESYESYESNOYES
Sheet1


Any help would be appreciated. Thanks in advance!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is whatever is in the hidden rows likely to affect any formula calculations?
 
Upvote 0
Hi cma,

**Note Peter SS comment regarding your filtered rows and columns...

I've converted your punch down report with Power Query and tested against in & out times with a 1 hour tolerance of the time slot.

Book1
ABCDEFG
1DateFirstLastColumn1In TimeOut TimeRole
230/11/2021WaynePaul11:00:00 AM5:07:00 PMBARTENDER
330/11/2021JustinBrassard11:00:00 AM2:23:00 PMGREETER
430/11/2021ShishirShekh11:06:00 AM4:58:00 PMBUSSER
530/11/2021TaliTreister11:30:00 AM6:47:00 PMGREETER
630/11/2021AbigaelleVasseur4:25:00 PM10:04:00 PMGREETER
730/11/2021StephanieCarter5:05:00 PM10:35:00 PMBARTENDER
81/12/2021JustinBrassard11:00:00 AM5:00:00 PMGREETER
91/12/2021DannyChin11:05:00 AM4:59:00 PMBARTENDER
101/12/2021ShishirShekh11:18:00 AM4:58:00 PMBUSSER
111/12/2021GenevieveTurgeon5:00:00 PM10:33:00 PMBARTENDER
121/12/2021Daniel-DominikDumont5:00:00 PM11:15:00 PMBUSSER
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"In Time", type time}, {"Out Time", type time}, {"Date", type date}})
in
    #"Changed Type"

Book1
ABCDEFG
13
14Tolerance4:00:00 PM6:00:00 PM
15
16AMAMAMPMPMPM
17DateBUSSERGREETERBARTENDERBUSSERGREETERBARTENDER
1830/11/2021YesYesYesNoYesYes
191/12/2021YesYesYesYesNoYes
20
21BUSSERGREETERBARTENDERBUSSERGREETERBARTENDER
2230/11/2021YESYESYESNOYESYES
231/12/2021YESYESYESYESNOYES
Sheet1
Cell Formulas
RangeFormula
B18:G19B18=IF(AND(B$16="AM",SUMPRODUCT((Table1_2[Date]=$A18)*(Table1_2[Role]=B$17)*(Table1_2[In Time]<$C$14)*(Table1_2[Out Time]<$C$14))=1),"Yes",IF(AND(B$16="PM",SUMPRODUCT((Table1_2[Date]=$A18)*(Table1_2[Role]=B$17)*(Table1_2[In Time]>$B$14)*(Table1_2[Out Time]>$B$14))=1),"Yes","No"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B18:G19Cell Valuecontains "No"textNO
 
Upvote 0
Hi CMacnab,

With so many people clocking in and out around 5pm I'd suggest a band of some kind so I've added "Band in hours". With the setting of 2 it will count In Time of 3pm or earlier as AM work, an Out Time of 7pm or later as PM work and if both are true (e.g. clock in 2pm and clock out 8pm) then it counts as both AM and PM.

I have ignored the hidden rows.

The time formats are not recognized by Excel, hence the ugly sets of TRIM and SUBSTITUTE functions.

CMacnab.xlsx
ABCDEFGH
1DateFirstLastIn TimeOut TimeRole
230-Nov-21WaynePaul11:00AM 5:07PMBARTENDER
330-Nov-21JustinBrassard11:00AM 2:23PMGREETER
430-Nov-21ShishirShekh11:06AM 4:58PMBUSSER
530-Nov-21TaliTreister11:30AM 6:47PMGREETER
630-Nov-21AbigaelleVasseur4:25PM 10:04PMGREETER
730-Nov-21StephanieCarter5:05PM 10:35PMBARTENDER
801-Dec-21JustinBrassard11:00AM 5:00PMGREETER
901-Dec-21DannyChin11:05AM 4:59PMBARTENDER
1001-Dec-21ShishirShekh11:18AM 4:58PMBUSSER
1101-Dec-21GenevieveTurgeon5:00PM 10:33PMBARTENDER
1201-Dec-21Daniel-DominikDumont5:00PM 11:15PMBUSSER
13
Punch Report


CMacnab.xlsx
ABCDEFGH
1AMPMBand in hours
2DateBUSSERGREETERBARTENDERBUSSERGREETERBARTENDER2
330-Nov-21YESYESYESNOYESYES
401-Dec-21YESYESYESYESNOYES
502-Dec-21      
603-Dec-21      
704-Dec-21      
805-Dec-21      
Sheet1
Cell Formulas
RangeFormula
A3A3=MIN('Punch Report'!$B$2:$B$17)
B3:D8B3=IF(ISNA(MATCH($A3,'Punch Report'!$B$2:$B$19,0)),"",IF(ISNUMBER(AGGREGATE(15,6,ROW('Punch Report'!$F$2:$F$19)-ROW('Punch Report'!$F$1)/(('Punch Report'!$B$2:$B$19=$A3)*(TIME(17,0,0)-SUBSTITUTE(SUBSTITUTE(TRIM('Punch Report'!$F$2:$F$19),"AM"," AM"),"PM"," PM")>TIME($H$2,0,0))*('Punch Report'!$H$2:$H$19=B$2)),1)),"YES","NO"))
E3:G8E3=IF(ISNA(MATCH($A3,'Punch Report'!$B$2:$B$19,0)),"",IF(ISNUMBER(AGGREGATE(15,6,ROW('Punch Report'!$F$2:$F$19)-ROW('Punch Report'!$F$1)/(('Punch Report'!$B$2:$B$19=$A3)*(SUBSTITUTE(SUBSTITUTE(TRIM('Punch Report'!$G$2:$G$19),"AM"," AM"),"PM"," PM")-TIME(17,0,0)>TIME($H$2,0,0))*('Punch Report'!$H$2:$H$19=B$2)),1)),"YES","NO"))
A4:A8A4=A3+1
 
Upvote 0
Peter_SSs said:
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is whatever is in the hidden rows likely to affect any formula calculations?
Click to expand...
Thank you Peter, done. No, the hidden columns won't affect the calculations.
 
Upvote 0
RasGhul said:
Hi cma,

**Note Peter SS comment regarding your filtered rows and columns...

I've converted your punch down report with Power Query and tested against in & out times with a 1 hour tolerance of the time slot.

Book1
ABCDEFG
1DateFirstLastColumn1In TimeOut TimeRole
230/11/2021WaynePaul11:00:00 AM5:07:00 PMBARTENDER
330/11/2021JustinBrassard11:00:00 AM2:23:00 PMGREETER
430/11/2021ShishirShekh11:06:00 AM4:58:00 PMBUSSER
530/11/2021TaliTreister11:30:00 AM6:47:00 PMGREETER
630/11/2021AbigaelleVasseur4:25:00 PM10:04:00 PMGREETER
730/11/2021StephanieCarter5:05:00 PM10:35:00 PMBARTENDER
81/12/2021JustinBrassard11:00:00 AM5:00:00 PMGREETER
91/12/2021DannyChin11:05:00 AM4:59:00 PMBARTENDER
101/12/2021ShishirShekh11:18:00 AM4:58:00 PMBUSSER
111/12/2021GenevieveTurgeon5:00:00 PM10:33:00 PMBARTENDER
121/12/2021Daniel-DominikDumont5:00:00 PM11:15:00 PMBUSSER
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"In Time", type time}, {"Out Time", type time}, {"Date", type date}})
in
    #"Changed Type"

Book1
ABCDEFG
13
14Tolerance4:00:00 PM6:00:00 PM
15
16AMAMAMPMPMPM
17DateBUSSERGREETERBARTENDERBUSSERGREETERBARTENDER
1830/11/2021YesYesYesNoYesYes
191/12/2021YesYesYesYesNoYes
20
21BUSSERGREETERBARTENDERBUSSERGREETERBARTENDER
2230/11/2021YESYESYESNOYESYES
231/12/2021YESYESYESYESNOYES
Sheet1
Cell Formulas
RangeFormula
B18:G19B18=IF(AND(B$16="AM",SUMPRODUCT((Table1_2[Date]=$A18)*(Table1_2[Role]=B$17)*(Table1_2[In Time]<$C$14)*(Table1_2[Out Time]<$C$14))=1),"Yes",IF(AND(B$16="PM",SUMPRODUCT((Table1_2[Date]=$A18)*(Table1_2[Role]=B$17)*(Table1_2[In Time]>$B$14)*(Table1_2[Out Time]>$B$14))=1),"Yes","No"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B18:G19Cell Valuecontains "No"textNO
Click to expand...
As elegant as this seems, I'm running an older version of Excel so I don't think a power query is possible. Can this work without that?
 
Upvote 0
Hi Toadstool, I think this is close but not quite working. I had to modify the formulas as I only gave you part of the data. Basically I removed the row numbers as I normally reference the whole column as the number of lines can change from week to week. I don't know if I maybe modified something incorrectly. This is the full data (the hidden lines are job roles that we are not looking for). I couldn't include all of the data as it's too many cells but I think this will give you a better idea. And yes, it is the date & times that are causing this headache, lol.

PP25-2021 Reuben's (2020-11-29 to 2021-12-12).xlsx
BCDFGH
200Sun-5-Dec-21Daniel-DominikDumont11:01AM 6:51PMBUSSER
208Sun-5-Dec-21TarehaTaillon11:40AM 8:11PMGREETER
209Sun-5-Dec-21GenevieveTurgeon12:00PM 5:00PMBARTENDER
219Sun-5-Dec-21StephanieCarter5:00PM 10:37PMBARTENDER
221Sun-5-Dec-21JerryDugue5:00PM 10:45PMBUSSER
223Sun-5-Dec-21Lauren RoseFaria5:00PM 10:04PMGREETER
232Tue-7-Dec-21WaynePaul11:00AM 4:31PMBARTENDER
235Tue-7-Dec-21JustinBrassard11:00AM 1:08PMGREETER
239Tue-7-Dec-21ShishirShekh11:21AM 4:57PMBUSSER
241Tue-7-Dec-21TaliTreister11:30AM 6:52PMGREETER
253Tue-7-Dec-21StephanieCarter5:00PM 10:39PMBARTENDER
254Tue-7-Dec-21MohiUddin5:00PM 10:39PMBUSSER
256Tue-7-Dec-21AbigaelleVasseur5:00PM 10:27PMGREETER
262Tue-7-Dec-21BoubacarKa5:04PM 7:57PMBUSSER
273Wed-8-Dec-21TaliTreister11:22AM 5:02PMGREETER
274Wed-8-Dec-21DannyChin11:28AM 5:00PMBARTENDER
280Wed-8-Dec-21ShishirShekh11:45AM 5:00PMBUSSER
287Wed-8-Dec-21Daniel-DominikDumont5:00PM 10:56PMBUSSER
293Wed-8-Dec-21GenevieveTurgeon5:08PM 10:22PMBARTENDER
294Wed-8-Dec-21AbigaelleVasseur5:14PM 10:16PMGREETER
304Thu-9-Dec-21JustinBrassard11:00AM 5:04PMGREETER
309Thu-9-Dec-21DannyChin11:08AM 4:54PMBARTENDER
310Thu-9-Dec-21JerryDugue11:10AM 8:21PMBUSSER
321Thu-9-Dec-21AndresRojo5:00PM 10:48PMBARTENDER
322Thu-9-Dec-21BoubacarKa5:00PM 10:55PMBUSSER
324Thu-9-Dec-21TarehaTaillon5:00PM 10:10PMGREETER
334Thu-9-Dec-21AbigaelleVasseur5:05PM 10:20PMGREETER
339Fri-10-Dec-21JerryDugue10:00AM 5:13PMBUSSER
341Fri-10-Dec-21AbigaelleVasseur10:46AM 9:49PMGREETER
342Fri-10-Dec-21SamNoorafshar11:00AM 4:54PMBARTENDER
351Fri-10-Dec-21JustinBrassard11:04AM 2:11PMGREETER
362Fri-10-Dec-21DeborahShewchuk4:00PM 11:43PMGREETER
364Fri-10-Dec-21GenevieveTurgeon5:00PM 12:13AMBARTENDER
365Fri-10-Dec-21ShishirShekh5:00PM 12:38AMBUSSER
382Sat-11-Dec-21JerryDugue11:00AM 5:11PMBUSSER
388Sat-11-Dec-21WaynePaul11:07AM 4:18PMBARTENDER
389Sat-11-Dec-21JustinBrassard11:10AM 12:41PMGREETER
391Sat-11-Dec-21TarehaTaillon11:30AM 4:08PMGREETER
398Sat-11-Dec-21BoubacarKa2:03PM 7:41PMBUSSER
402Sat-11-Dec-21GenevieveTurgeon4:00PM 11:35PMBARTENDER
405Sat-11-Dec-21Lauren RoseFaria4:00PM 11:31PMGREETER
406Sat-11-Dec-21Lauren RoseFaria4:00PM 4:00PMGREETER
411Sat-11-Dec-21ShishirShekh5:00PM 12:26AMBUSSER
413Sat-11-Dec-21AbigaelleVasseur5:00PM 8:32PMGREETER
424Sun-12-Dec-21AndresRojo11:00AM 5:00PMBARTENDER
434Sun-12-Dec-21TarehaTaillon11:40AM 8:51PMGREETER
439Sun-12-Dec-21JerryDugue2:00PM 10:51PMBUSSER
444Sun-12-Dec-21StephanieCarter5:00PM 11:14PMBARTENDER
445Sun-12-Dec-21Lauren RoseFaria5:00PM 10:00PMGREETER
Punch Report


This is the results on the table, the 10th should show all YES, the 12th should have a NO for AM BUSSER, and the 6th should be all NOs. I think the time might have to be respresented by a range. I.e. if present between 5am & 5pm = AM and if present between 5pm & 5am = PM.
PP25-2021 Reuben's (2020-11-29 to 2021-12-12).xlsx
ABCDEFGHI
1AMPMBand in Hrs
2DateBARTENDERBUSSERGREETERBARTENDERBUSSERGREETER2
3Mon-29-Nov-21NONONONONONO
4Tue-30-Nov-21YESYESYESYESNOYES
5Wed-1-Dec-21YESYESYESYESYESYES
6Thu-2-Dec-21YESYESYESYESYESYES
7Fri-3-Dec-21YESYESYESYESYESYES
8Sat-4-Dec-21YESYESYESYESYESYES
9Sun-5-Dec-21YESYESYESYESYESYES
10Mon-6-Dec-21      
11Tue-7-Dec-21YESYESYESYESYESYES
12Wed-8-Dec-21YESYESYESYESYESYES
13Thu-9-Dec-21YESYESYESYESYESYES
14Fri-10-Dec-21YESYESYESNONOYESs/b all YES
15Sat-11-Dec-21YESYESYESYESYESYES
16Sun-12-Dec-21YESYESYESYESYESYESBUSSER AM s/b NO
Support Staff
Cell Formulas
RangeFormula
A3A3=MIN('Punch Report'!$B:$B)
B3:D16B3=IF(ISNA(MATCH($A3,'Punch Report'!$B:$B,0)),"",IF(ISNUMBER(AGGREGATE(15,6,ROW('Punch Report'!$F:$F)-ROW('Punch Report'!$F:$F)/(('Punch Report'!$B:$B=$A3)*(TIME(17,0,0)-SUBSTITUTE(SUBSTITUTE(TRIM('Punch Report'!$F:$F),"AM"," AM"),"PM"," PM")>TIME($H$2,0,0))*('Punch Report'!$H:$H=B$2)),1)),"YES","NO"))
E3:G16E3=IF(ISNA(MATCH($A3,'Punch Report'!$B:$B,0)),"",IF(ISNUMBER(AGGREGATE(15,6,ROW('Punch Report'!$F:$F)-ROW('Punch Report'!$F:$F)/(('Punch Report'!$B:$B=$A3)*(SUBSTITUTE(SUBSTITUTE(TRIM('Punch Report'!$G:$G),"AM"," AM"),"PM"," PM")-TIME(17,0,0)>TIME($H$2,0,0))*('Punch Report'!$H:$H=B$2)),1)),"YES","NO"))
A4:A16A4=A3+1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:G16Cell Valuecontains "NO"textNO


This is how I modified your formulas:
B3:D16 =IF(ISNA(MATCH($A3,'Punch Report'!$B:$B,0)),"",IF(ISNUMBER(AGGREGATE(15,6,ROW('Punch Report'!$F:$F)-ROW('Punch Report'!$F:$F)/(('Punch Report'!$B:$B=$A3)*(TIME(17,0,0)-SUBSTITUTE(SUBSTITUTE(TRIM('Punch Report'!$F:$F),"AM"," AM"),"PM"," PM")>TIME($H$2,0,0))*('Punch Report'!$H:$H=B$2)),1)),"YES","NO"))

E3:G16 =IF(ISNA(MATCH($A3,'Punch Report'!$B:$B,0)),"",IF(ISNUMBER(AGGREGATE(15,6,ROW('Punch Report'!$F:$F)-ROW('Punch Report'!$F:$F)/(('Punch Report'!$B:$B=$A3)*(SUBSTITUTE(SUBSTITUTE(TRIM('Punch Report'!$G:$G),"AM"," AM"),"PM"," PM")-TIME(17,0,0)>TIME($H$2,0,0))*('Punch Report'!$H:$H=B$2)),1)),"YES","NO"))
 
Upvote 0
Hi CMcnab,

Some functions don't work with full column addresses, and it took over a minute to recalculate so I won't go that direction. I'll provide one which processes 10,000 rows and you can amend if you really have more.

You say the 10th should show all "YES" but your first data example had nobody working over midnight so the challenge is with a checkout after midnight then it's no longer 10th December but the 11th.

If I use "if present between 5am & 5pm = AM and if present between 5pm & 5am = PM." then In Time 4:30PM and Out Time 5:30PM would register as both, that's why I introduced the band.

Even then there are challenges because on the 12th December you have a BUSSER clocking in at 2pm so I classed them as AM, but they clocked out at 10:51pm so should they be PM, or both?
Have another think and I can provide a formula, base on In Time, Out Time, most hours worked before/after 5pm and even class some as both AM and PM if you want.


With these data challenges, and given much of the logic is now doing data cleanup, I've taken an entirely different approach and created a Work sheet. It does the day/time fixes and decides if it's AM or PM so making the Support Staff sheet much simpler.

Let me know what you think.

I'm only showing the first part of your Punch Report but all data was used. I'm only showing a few rows of Work but the formulae can copy down to 10,000 and it calculates quite quickly.

CMacnab-v2.xlsx
BCDEFGH
1DateFirstLastIn TimeOut TimeRole
20044535Daniel-DominikDumont11:01AM 6:51PMBUSSER
20844535TarehaTaillon11:40AM 8:11PMGREETER
20944535GenevieveTurgeon12:00PM 5:00PMBARTENDER
21944535StephanieCarter5:00PM 10:37PMBARTENDER
220
22144535JerryDugue5:00PM 10:45PMBUSSER
222
22344535Lauren RoseFaria5:00PM 10:04PMGREETER
Punch Report


CMacnab-v2.xlsx
ABCDEFGHIJKLMNO
1No. Data RowsRowDateFirstLastIn TimeOut TimeRoleInOutDate/Time InDate/Time OutAM or PMHours Worked
250200Sun-5-Dec-21Daniel-DominikDumont11:01AM 6:51PMBUSSER11:01 AM6:51 PM5-Dec-21 11:015-Dec-21 18:51AM07:50
3208Sun-5-Dec-21TarehaTaillon11:40AM 8:11PMGREETER11:40 AM8:11 PM5-Dec-21 11:405-Dec-21 20:11AM08:31
4Band in Hrs209Sun-5-Dec-21GenevieveTurgeon12:00PM 5:00PMBARTENDER12:00 PM5:00 PM5-Dec-21 12:005-Dec-21 17:00AM05:00
52219Sun-5-Dec-21StephanieCarter5:00PM 10:37PMBARTENDER5:00 PM10:37 PM5-Dec-21 17:005-Dec-21 22:37PM05:37
6221Sun-5-Dec-21JerryDugue5:00PM 10:45PMBUSSER5:00 PM10:45 PM5-Dec-21 17:005-Dec-21 22:45PM05:45
7Days in Range223Sun-5-Dec-21Lauren RoseFaria5:00PM 10:04PMGREETER5:00 PM10:04 PM5-Dec-21 17:005-Dec-21 22:04PM05:04
87232Tue-7-Dec-21WaynePaul11:00AM 4:31PMBARTENDER11:00 AM4:31 PM7-Dec-21 11:007-Dec-21 16:31AM05:31
9235Tue-7-Dec-21JustinBrassard11:00AM 1:08PMGREETER11:00 AM1:08 PM7-Dec-21 11:007-Dec-21 13:08AM02:08
10239Tue-7-Dec-21ShishirShekh11:21AM 4:57PMBUSSER11:21 AM4:57 PM7-Dec-21 11:217-Dec-21 16:57AM05:36
11241Tue-7-Dec-21TaliTreister11:30AM 6:52PMGREETER11:30 AM6:52 PM7-Dec-21 11:307-Dec-21 18:52AM07:22
12253Tue-7-Dec-21StephanieCarter5:00PM 10:39PMBARTENDER5:00 PM10:39 PM7-Dec-21 17:007-Dec-21 22:39PM05:39
13254Tue-7-Dec-21MohiUddin5:00PM 10:39PMBUSSER5:00 PM10:39 PM7-Dec-21 17:007-Dec-21 22:39PM05:39
Work
Cell Formulas
RangeFormula
A2A2=COUNTA('Punch Report'!$B:$B)
B2:B13B2=IF(ROW()>$A$2,"",AGGREGATE(15,6,ROW('Punch Report'!$B$2:$B$9999)/('Punch Report'!$B$2:$B$9999<>""),ROW()-ROW($B$1)))
G2:I13,C2:E13C2=IF($B2="","",INDEX('Punch Report'!B:B,$B2))
J2:K13J2=IF($B2="","",(SUBSTITUTE(SUBSTITUTE(TRIM(G2),"AM"," AM"),"PM"," PM")+0))
L2:L13L2=IF($B2="","",C2+J2)
M2:M13M2=IF($B2="","",IF(K2<J2,C2+1+K2,C2+K2))
N2:N13N2=IF($B2="","",IF(L2<C2+TIME(17,0,0)-TIME($A$5,0,0),"AM",IF(M2>C2+TIME(17,0,0)+TIME($A$5,0,0),"PM","")))
O2:O13O2=IF($B2="","",M2-L2)
A8A8=MAX($C:$C)-MIN($C:$C)


CMacnab-v2.xlsx
ABCDEFG
1BARTENDERBUSSERGREETERBARTENDERBUSSERGREETER
2DateAMAMAMPMPMPM
3Sun-5-Dec-21YESYESYESYESYESYES
4Mon-6-Dec-21      
5Tue-7-Dec-21YESYESYESYESYESYES
6Wed-8-Dec-21YESYESYESYESYESYES
7Thu-9-Dec-21YESYESYESYESYESYES
8Fri-10-Dec-21YESYESYESYESYESYES
9Sat-11-Dec-21YESYESYESYESYESYES
10Sun-12-Dec-21YESYESYESYESNOYES
11       
12       
Support Staff
Cell Formulas
RangeFormula
A3A3=MIN(Work!$C:$C)
B3:G12B3=IF($A3="","",IF(ISNA(MATCH($A3,Work!$C$2:$C$10000,0)),"",IF(ISNUMBER(AGGREGATE(15,6,ROW(Work!$C$2:$C$10000)-ROW(Work!$C$1)/((Work!$C$2:$C$10000=$A3)*(Work!$N$2:$N$10000=B$2)*(Work!$I$2:$I$10000=B$1)),1)),"YES","NO")))
A4:A12A4=IF(ROW()>ROW($A$3)+Work!$A$8,"",A3+1)
 
Upvote 0
cmacnab said:
As elegant as this seems, I'm running an older version of Excel so I don't think a power query is possible. Can this work without that?
Click to expand...

Excel 2016 has Get & Transform - this makes it really easy to do the data clean up that Toadstool demonstrated with his conversion table.

If your not confident with power query, I would consider Toadstools solution.
 
Upvote 0
Toadstool said:
Hi CMcnab,

Some functions don't work with full column addresses, and it took over a minute to recalculate so I won't go that direction. I'll provide one which processes 10,000 rows and you can amend if you really have more.

You say the 10th should show all "YES" but your first data example had nobody working over midnight so the challenge is with a checkout after midnight then it's no longer 10th December but the 11th.

If I use "if present between 5am & 5pm = AM and if present between 5pm & 5am = PM." then In Time 4:30PM and Out Time 5:30PM would register as both, that's why I introduced the band.

Even then there are challenges because on the 12th December you have a BUSSER clocking in at 2pm so I classed them as AM, but they clocked out at 10:51pm so should they be PM, or both?
Have another think and I can provide a formula, base on In Time, Out Time, most hours worked before/after 5pm and even class some as both AM and PM if you want.


With these data challenges, and given much of the logic is now doing data cleanup, I've taken an entirely different approach and created a Work sheet. It does the day/time fixes and decides if it's AM or PM so making the Support Staff sheet much simpler.

Let me know what you think.

I'm only showing the first part of your Punch Report but all data was used. I'm only showing a few rows of Work but the formulae can copy down to 10,000 and it calculates quite quickly.

CMacnab-v2.xlsx
BCDEFGH
1DateFirstLastIn TimeOut TimeRole
20044535Daniel-DominikDumont11:01AM 6:51PMBUSSER
20844535TarehaTaillon11:40AM 8:11PMGREETER
20944535GenevieveTurgeon12:00PM 5:00PMBARTENDER
21944535StephanieCarter5:00PM 10:37PMBARTENDER
220
22144535JerryDugue5:00PM 10:45PMBUSSER
222
22344535Lauren RoseFaria5:00PM 10:04PMGREETER
Punch Report


CMacnab-v2.xlsx
ABCDEFGHIJKLMNO
1No. Data RowsRowDateFirstLastIn TimeOut TimeRoleInOutDate/Time InDate/Time OutAM or PMHours Worked
250200Sun-5-Dec-21Daniel-DominikDumont11:01AM 6:51PMBUSSER11:01 AM6:51 PM5-Dec-21 11:015-Dec-21 18:51AM07:50
3208Sun-5-Dec-21TarehaTaillon11:40AM 8:11PMGREETER11:40 AM8:11 PM5-Dec-21 11:405-Dec-21 20:11AM08:31
4Band in Hrs209Sun-5-Dec-21GenevieveTurgeon12:00PM 5:00PMBARTENDER12:00 PM5:00 PM5-Dec-21 12:005-Dec-21 17:00AM05:00
52219Sun-5-Dec-21StephanieCarter5:00PM 10:37PMBARTENDER5:00 PM10:37 PM5-Dec-21 17:005-Dec-21 22:37PM05:37
6221Sun-5-Dec-21JerryDugue5:00PM 10:45PMBUSSER5:00 PM10:45 PM5-Dec-21 17:005-Dec-21 22:45PM05:45
7Days in Range223Sun-5-Dec-21Lauren RoseFaria5:00PM 10:04PMGREETER5:00 PM10:04 PM5-Dec-21 17:005-Dec-21 22:04PM05:04
87232Tue-7-Dec-21WaynePaul11:00AM 4:31PMBARTENDER11:00 AM4:31 PM7-Dec-21 11:007-Dec-21 16:31AM05:31
9235Tue-7-Dec-21JustinBrassard11:00AM 1:08PMGREETER11:00 AM1:08 PM7-Dec-21 11:007-Dec-21 13:08AM02:08
10239Tue-7-Dec-21ShishirShekh11:21AM 4:57PMBUSSER11:21 AM4:57 PM7-Dec-21 11:217-Dec-21 16:57AM05:36
11241Tue-7-Dec-21TaliTreister11:30AM 6:52PMGREETER11:30 AM6:52 PM7-Dec-21 11:307-Dec-21 18:52AM07:22
12253Tue-7-Dec-21StephanieCarter5:00PM 10:39PMBARTENDER5:00 PM10:39 PM7-Dec-21 17:007-Dec-21 22:39PM05:39
13254Tue-7-Dec-21MohiUddin5:00PM 10:39PMBUSSER5:00 PM10:39 PM7-Dec-21 17:007-Dec-21 22:39PM05:39
Work
Cell Formulas
RangeFormula
A2A2=COUNTA('Punch Report'!$B:$B)
B2:B13B2=IF(ROW()>$A$2,"",AGGREGATE(15,6,ROW('Punch Report'!$B$2:$B$9999)/('Punch Report'!$B$2:$B$9999<>""),ROW()-ROW($B$1)))
G2:I13,C2:E13C2=IF($B2="","",INDEX('Punch Report'!B:B,$B2))
J2:K13J2=IF($B2="","",(SUBSTITUTE(SUBSTITUTE(TRIM(G2),"AM"," AM"),"PM"," PM")+0))
L2:L13L2=IF($B2="","",C2+J2)
M2:M13M2=IF($B2="","",IF(K2<J2,C2+1+K2,C2+K2))
N2:N13N2=IF($B2="","",IF(L2<C2+TIME(17,0,0)-TIME($A$5,0,0),"AM",IF(M2>C2+TIME(17,0,0)+TIME($A$5,0,0),"PM","")))
O2:O13O2=IF($B2="","",M2-L2)
A8A8=MAX($C:$C)-MIN($C:$C)


CMacnab-v2.xlsx
ABCDEFG
1BARTENDERBUSSERGREETERBARTENDERBUSSERGREETER
2DateAMAMAMPMPMPM
3Sun-5-Dec-21YESYESYESYESYESYES
4Mon-6-Dec-21      
5Tue-7-Dec-21YESYESYESYESYESYES
6Wed-8-Dec-21YESYESYESYESYESYES
7Thu-9-Dec-21YESYESYESYESYESYES
8Fri-10-Dec-21YESYESYESYESYESYES
9Sat-11-Dec-21YESYESYESYESYESYES
10Sun-12-Dec-21YESYESYESYESNOYES
11       
12       
Support Staff
Cell Formulas
RangeFormula
A3A3=MIN(Work!$C:$C)
B3:G12B3=IF($A3="","",IF(ISNA(MATCH($A3,Work!$C$2:$C$10000,0)),"",IF(ISNUMBER(AGGREGATE(15,6,ROW(Work!$C$2:$C$10000)-ROW(Work!$C$1)/((Work!$C$2:$C$10000=$A3)*(Work!$N$2:$N$10000=B$2)*(Work!$I$2:$I$10000=B$1)),1)),"YES","NO")))
A4:A12A4=IF(ROW()>ROW($A$3)+Work!$A$8,"",A3+1)
Click to expand...
Thank you Toadstool, I will try this out and see how it works. I have an additional complication with this file, it's a beast, lol. I will go find my post on that problem as someone had provided a solution but using a new data file, the solution is not working now. It might have even been you that provided the solution. Have a great day!
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Combobox search dropdown triggering when editing other sheets
2
Replies
10
Views
553
Akuini
Akuini
K
  • Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
253
Eric W
Eric W
T
  • Question
How do you pause a Macro to move the cursor from the cell it is on to another cell
2
Replies
13
Views
820
Trebor J
T
S
  • Question
Countifs function for Date & Time Range not counting all cells
2
Replies
12
Views
960
SMcCafferty
S
C
  • Solved
YES or NO function with 3 criteria
Replies
3
Views
207
Fluff
Fluff

Forum statistics

Threads
1,215,181
Messages
6,123,508
Members
449,101
Latest member
mgro123

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top