TIme Clock record reformatting to ???

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello All,
I'd like some help formatting this (Tbl-A)(Loaded via Power Query)
PQdata.png


into this (Tbl-B)
FormattedData.png


What I used to populate Tbl-B Columns (All the same Index/Match formula logic):
Employee Id - IFERROR(INDEX($B$2:$B$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Time In - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Time Out - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$2, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")

Problems:
  • If someone fails to clock in or out, the entire column is thrown off
  • The In and out times are not linked to the Employee ID so they are essentially listed in order of appearance. The red on Tbl-B indicates the start of error (Matching Tbl-A)
    BothPics.png
Also, the new shifts are going to be 12 hour days and 12 hour nights, and there will ALWAYS be times that folks forget to clock in or out. So is there a way to "skip" a clock out if over 15 hours from clock in time.

Please let me know the best way of going about this whether in Power Query or excel.
Many thanks for any help with this
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,846
Office Version
  1. 2019
Platform
  1. Windows
Could you post your examples with XL2BB please, we can't test ideas on pictures.

As you mention 12 hour nights, I assume that clock out will be the day after clock in. Rather than using over 15 hours (which would most likely fall somewhere somewhere between complex and impossible) is there a fixed window where shifts would always start and end? What about anyone doing overtime?
 

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Good idea,
And yes Jasonb75, you're absolutely right about night shift clock out times being logged as the following day.

Typical schedule is 4 shifts at 12 hours
Overtime are any hours that exceed 10 per shift and over 40 per week
(employees will typically be working 10 to 12 hour shifts so they will log 2 hours per shift for 3 shifts then on the fourth shift they will log 4 hours of regular and 8 hours of overtime)
Example:
PunchCard(Temps).xlsm
ABC
110212
210212
310212
44812
54014
Sheet1
Cell Formulas
RangeFormula
C1:C4C1=A1+B1
A5A5=SUM(C1:C3)+A4
B5B5=SUM(B1:B4)



(Note: I've changed Employee ID column in Tbl-B to Vlookup on the Tbl-B Time In column so that those two at least always match)
But, I think that I could simply be completely off on my strategy, so I'm very open to suggestions.
(Note: examples below don't reflect the new hire shifts of 10 to 12 hour day and night)

PunchCard(Temps).xlsm
ABCDEFGHIJKL
1Submission DateEmployee ID:No LabelIPSubmission IDEdit LinkEmployee IDTIME INTIME OUTClock IN
28/6/20 9:02 AM8907Clock IN96.65.210.854.72532E+18Edit Submission89078/6/20 9:02 AM8/6/20 5:05 PMClock OUT
38/6/20 11:21 AM6422Clock IN96.65.210.854.72558E+18Edit Submission64228/6/20 11:21 AM8/6/20 10:07 PM
48/6/20 5:05 PM8907Clock OUT96.65.210.854.72561E+18Edit Submission89078/7/20 9:05 AM8/7/20 4:59 PM
58/6/20 10:07 PM6422Clock OUT96.65.210.854.72579E+18Edit Submission64228/7/20 4:49 PM8/10/20 5:08 PM
68/7/20 9:05 AM8907Clock IN96.65.210.854.72618E+18Edit Submission89078/10/20 9:09 AM8/11/20 9:56 AM
78/7/20 4:49 PM6422Clock IN96.65.210.854.72646E+18Edit Submission64228/10/20 10:24 AM8/11/20 5:09 PM
88/7/20 4:59 PM8907Clock OUT96.65.210.854.72647E+18Edit Submission89078/11/20 9:04 AM8/12/20 5:02 PM
98/10/20 9:09 AM8907Clock IN96.65.210.854.72878E+18Edit Submission64228/11/20 9:56 AM8/13/20 5:02 PM
108/10/20 10:24 AM6422Clock IN96.65.210.854.72882E+18Edit Submission89078/12/20 9:04 AM 
118/10/20 5:08 PM8907Clock OUT96.65.210.854.72907E+18Edit Submission89078/13/20 9:05 AM 
128/11/20 9:04 AM8907Clock IN96.65.210.854.72964E+18Edit Submission
138/11/20 9:56 AM6422Clock OUT96.65.210.854.72967E+18Edit Submission
148/11/20 9:56 AM6422Clock IN96.65.210.854.72967E+18Edit Submission
158/11/20 5:09 PM8907Clock OUT96.65.210.854.72993E+18Edit Submission
168/12/20 9:04 AM8907Clock IN96.65.210.854.7305E+18Edit Submission
178/12/20 5:02 PM8907Clock OUT96.65.210.854.73079E+18Edit Submission
188/13/20 9:05 AM8907Clock IN96.65.210.854.73137E+18Edit Submission
198/13/20 5:02 PM8907Clock OUT96.65.210.854.73165E+18Edit Submission
DATA
Cell Formulas
RangeFormula
H2:H11H2=IFERROR(VLOOKUP([@[TIME IN]],A:B,2,FALSE),"")
I2:I11I2=IFERROR(INDEX($A$2:$A$1002, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1002)), MATCH(ROW($C$2:$C$1002), ROW($C$2:$C$1002))), ROWS($A$2:A2))),"")
J2:J11J2=IFERROR(INDEX($A$2:$A$1002, SMALL(IF(ISNUMBER(SEARCH($L$2, $C$2:$C$1002)), MATCH(ROW($C$2:$C$1002), ROW($C$2:$C$1002))), ROWS($A$2:A2))),"")
Named Ranges
NameRefers ToCells
ExternalData_1=DATA!$A$1:$F$19H2:H11
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,846
Office Version
  1. 2019
Platform
  1. Windows
I notice that your times have seconds as well, even though they are not visible in the formatting. As long as that means that it is impossible for 2 people to clock in at the exact same time then this should work. If 2 people do have identical in times then the first one on the list will be returned twice by vlookup and the other one will be missing.

This works with the clock in time when the clock out time is missing but not with the clock out time when the clock in is missing, I think that doing this both ways could be beyond the realistic scope of formulas.
dave77.xlsm
ABCDEFGHIJKL
1Submission DateEmployee ID:No LabelIPSubmission IDEdit LinkEmployee IDTIME INTIME OUTClock IN
28/6/20 09:02 AM8907Clock IN96.65.210.854.72532E+18Edit Submission89078/6/20 09:02 AM8/6/20 05:05 PMClock OUT
38/6/20 11:21 AM6422Clock IN96.65.210.854.72558E+18Edit Submission64228/6/20 11:21 AM8/6/20 10:07 PM
48/6/20 05:05 PM8907Clock OUT96.65.210.854.72561E+18Edit Submission89078/7/20 09:05 AM8/7/20 04:59 PM
58/6/20 10:07 PM6422Clock OUT96.65.210.854.72579E+18Edit Submission64228/7/20 04:49 PM 
68/7/20 09:05 AM8907Clock IN96.65.210.854.72618E+18Edit Submission89078/10/20 09:09 AM8/10/20 05:08 PM
78/7/20 04:49 PM6422Clock IN96.65.210.854.72646E+18Edit Submission64228/10/20 10:24 AM8/11/20 09:56 AM
88/7/20 04:59 PM8907Clock OUT96.65.210.854.72647E+18Edit Submission89078/11/20 09:04 AM8/11/20 05:09 PM
98/10/20 09:09 AM8907Clock IN96.65.210.854.72878E+18Edit Submission64228/11/20 09:56 AM 
108/10/20 10:24 AM6422Clock IN96.65.210.854.72882E+18Edit Submission89078/12/20 09:04 AM8/12/20 05:02 PM
118/10/20 05:08 PM8907Clock OUT96.65.210.854.72907E+18Edit Submission89078/13/20 09:05 AM 
128/11/20 09:04 AM8907Clock IN96.65.210.854.72964E+18Edit Submission   
138/11/20 09:56 AM6422Clock OUT96.65.210.854.72967E+18Edit Submission   
148/11/20 09:56 AM6422Clock IN96.65.210.854.72967E+18Edit Submission   
158/11/20 05:09 PM8907Clock OUT96.65.210.854.72993E+18Edit Submission   
168/12/20 09:04 AM8907Clock IN96.65.210.854.7305E+18Edit Submission   
178/12/20 05:02 PM8907Clock OUT96.65.210.854.73079E+18Edit Submission   
188/13/20 09:05 AM8907Clock IN96.65.210.854.73137E+18Edit Submission   
198/13/20 05:02 PM8907Clock OUT96.65.210.854.73165E+18Edit Submission   
Sheet13
Cell Formulas
RangeFormula
H2:H19H2=IF(I2="","",IFERROR(VLOOKUP(I2,$A$2:$B$19,2,0),""))
I2:I19I2=IF(ROWS(I$2:I2)>COUNTIF($C$2:$C$19,$L$1),"",MINIFS($A$2:$A$19,$A$2:$A$19,">"&N(I1),$C$2:$C$19,$L$1))
J2:J19J2=IF(ROWS(J$2:J2)>COUNTIF($C$2:$C$19,$L$1),"",IFERROR(1/(1/MINIFS($A$2:$A$19,$A$2:$A$19,">"&I2,$B$2:$B$19,H2,$C$2:$C$19,$L$2,$A$2:$A$19,"<"&MINIFS($I$2:$I$12,$H$2:$H$12,H2,$I$2:$I$12,">"&I2))),""))
 

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi Jasonb75,
Everyone uses the same computer so there will never be two clock ins or outs at the same time to the second

Thanks for helping with this.
I changed all of the End Ranges from 19 and 12 to 10000 to accommodate for quite a few more Employees using the system
It works up until a point Date 8/18/20 7:02 am then repeats... I would really appreciate your recommendation.
TimeSheet(Temps).xlsm
ABCDEFGHIJKL
1Timezone UTCEmployee ID:No LabelIPEmployee IDTIME INTIME OUTClock IN
28/6/20 7:02 AM8907Clock IN96.65.210.8589078/6/20 7:02 AM8/6/20 3:05 PMClock OUT
38/6/20 9:21 AM6422Clock IN96.65.210.8564228/6/20 9:21 AM8/6/20 8:07 PM
48/6/20 3:05 PM8907Clock OUT96.65.210.8589078/7/20 7:05 AM8/7/20 2:49 PM
58/6/20 8:07 PM6422Clock OUT96.65.210.8564228/7/20 2:49 PM8/7/20 2:59 PM
68/7/20 7:05 AM8907Clock IN96.65.210.8589078/10/20 7:09 AM8/10/20 3:08 PM
78/7/20 2:49 PM6422Clock IN96.65.210.8564228/10/20 8:24 AM8/11/20 7:56 AM
88/7/20 2:49 PM8907Clock OUT96.65.210.8589078/11/20 7:04 AM8/11/20 3:09 PM
98/7/20 2:59 PM6422Clock OUT96.65.210.8664228/11/20 7:56 AM 
108/10/20 7:09 AM8907Clock IN96.65.210.8589078/12/20 7:04 AM8/12/20 3:02 PM
118/10/20 8:24 AM6422Clock IN96.65.210.8589078/13/20 7:05 AM8/13/20 3:02 PM
128/10/20 3:08 PM8907Clock OUT96.65.210.8589078/14/20 7:08 AM8/14/20 3:00 PM
138/11/20 5:08 PM6422Clock OUT96.65.210.8689078/17/20 7:06 AM8/17/20 3:11 PM
148/11/20 7:04 AM8907Clock IN96.65.210.8589078/18/20 7:02 AM 
158/11/20 7:56 AM6422Clock OUT96.65.210.8589078/18/20 7:02 AM 
168/11/20 7:56 AM6422Clock IN96.65.210.8589078/18/20 7:02 AM 
178/11/20 3:09 PM8907Clock OUT96.65.210.8589078/18/20 7:02 AM 
188/12/20 7:04 AM8907Clock IN96.65.210.8589078/18/20 7:02 AM 
198/12/20 3:02 PM8907Clock OUT96.65.210.8589078/18/20 7:02 AM 
208/13/20 7:05 AM8907Clock IN96.65.210.8589078/18/20 7:02 AM 
218/13/20 3:02 PM8907Clock OUT96.65.210.8589078/18/20 7:02 AM 
228/14/20 7:08 AM8907Clock IN96.65.210.8589078/18/20 7:02 AM 
238/14/20 3:00 PM8907Clock OUT96.65.210.8589078/18/20 7:02 AM 
248/17/20 7:06 AM8907Clock IN96.65.210.8589078/18/20 7:02 AM 
258/17/20 3:11 PM8907Clock OUT96.65.210.8589078/18/20 7:02 AM 
268/18/20 7:02 AM8907Clock IN96.65.210.8589078/18/20 7:02 AM 
278/18/20 3:03 PM8907Clock OUT96.65.210.8589078/18/20 7:02 AM 
288/19/20 7:02 AM8907Clock IN96.65.210.8589078/18/20 7:02 AM 
298/19/20 3:03 PM8907Clock OUT96.65.210.8589078/18/20 7:02 AM 
308/20/20 6:59 AM8907Clock IN96.65.210.8589078/18/20 7:02 AM 
318/20/20 3:03 PM8907Clock OUT96.65.210.8589078/18/20 7:02 AM 
328/21/20 3:03 PM8907Clock OUT96.65.210.85   
338/24/20 7:09 AM8907Clock IN96.65.210.85   
348/24/20 3:01 PM8907Clock OUT96.65.210.85   
358/25/20 7:07 AM8907Clock IN96.65.210.85   
368/25/20 3:01 PM8907Clock OUT96.65.210.85   
378/26/20 7:07 AM8907Clock IN96.65.210.85   
388/26/20 12:55 PM8907Clock OUT96.65.210.85   
398/27/20 7:04 AM8907Clock IN96.65.210.85   
408/27/20 3:00 PM8907Clock OUT96.65.210.85   
418/28/20 7:08 AM8907Clock IN96.65.210.85   
428/31/20 7:05 AM8907Clock IN96.65.210.85   
438/31/20 3:01 PM8907Clock OUT96.65.210.85   
449/1/20 7:08 AM8907Clock IN96.65.210.85   
459/1/20 2:59 PM8907Clock OUT96.65.210.85   
469/2/20 7:05 AM8907Clock IN96.65.210.85   
479/2/20 3:00 PM8907Clock OUT96.65.210.85   
489/3/20 7:03 AM8907Clock IN96.65.210.85   
499/3/20 3:02 PM8907Clock OUT96.65.210.85   
509/4/20 7:09 AM8907Clock IN96.65.210.85   
519/4/20 3:03 PM8907Clock OUT96.65.210.85   
529/7/20 7:05 AM8907Clock IN96.65.210.85   
539/7/20 3:01 PM8907Clock OUT96.65.210.85   
549/8/20 7:04 AM8907Clock IN96.65.210.85   
559/8/20 3:11 PM8907Clock OUT96.65.210.85   
569/9/20 7:03 AM8907Clock IN96.65.210.85   
579/9/20 3:06 PM8907Clock OUT96.65.210.85   
589/10/20 7:07 AM8907Clock IN96.65.210.85   
599/10/20 3:01 PM8907Clock OUT96.65.210.85   
609/11/20 7:09 AM8907Clock IN96.65.210.85   
619/11/20 3:01 PM8907Clock OUT96.65.210.85   
DATA
Cell Formulas
RangeFormula
H2:H61H2=IF(I2="","",IFERROR(VLOOKUP(I2,$A$2:$B$10000,2,0),""))
I2:I61I2=IF(ROWS(I$2:I2)>COUNTIF($C$2:$C$10000,$L$1),"",MINIFS($A$2:$A$10000,$A$2:$A$10000,">"&N(I1),$C$2:$C$10000,$L$1))
J2:J61J2=IF(ROWS(J$2:J2)>COUNTIF($C$2:$C$10000,$L$1),"",IFERROR(1/(1/MINIFS($A$2:$A$10000,$A$2:$A$10000,">"&I2,$B$2:$B$10000,H2,$C$2:$C$10000,$L$2,$A$2:$A$10000,"<"&MINIFS($I$2:$I$10000,$H$2:$H$10000,H2,$I$2:$I$10000,">"&I2))),""))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,846
Office Version
  1. 2019
Platform
  1. Windows
Do you have calculation on manual? I just copied the table above to excel and the results are coming out as expected. There is no sign of any rows repeating.
 

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi Jasonb75,
I've now verified that calculations are set to auto.
For some reason column H works until 8/18/20 7:02 AM then repeats 18 times.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,846
Office Version
  1. 2019
Platform
  1. Windows
I have absolutely no idea why that is happening, it works fine for me.
dave77.xlsm
ABCDEFGHIJKL
1Timezone UTCEmployee ID:No LabelIPEmployee IDTIME INTIME OUTClock IN
28/6/20 07:02 AM8907Clock IN96.65.210.8589078/6/20 07:02 AM8/6/20 03:05 PMClock OUT
38/6/20 09:21 AM6422Clock IN96.65.210.8564228/6/20 09:21 AM8/6/20 08:07 PM
48/6/20 03:05 PM8907Clock OUT96.65.210.8589078/7/20 07:05 AM8/7/20 02:49 PM
58/6/20 08:07 PM6422Clock OUT96.65.210.8564228/7/20 02:49 PM8/7/20 02:59 PM
68/7/20 07:05 AM8907Clock IN96.65.210.8589078/10/20 07:09 AM8/10/20 03:08 PM
78/7/20 02:49 PM6422Clock IN96.65.210.8564228/10/20 08:24 AM8/11/20 07:56 AM
88/7/20 02:49 PM8907Clock OUT96.65.210.8589078/11/20 07:04 AM8/11/20 03:09 PM
98/7/20 02:59 PM6422Clock OUT96.65.210.8664228/11/20 07:56 AM 
108/10/20 07:09 AM8907Clock IN96.65.210.8589078/12/20 07:04 AM8/12/20 03:02 PM
118/10/20 08:24 AM6422Clock IN96.65.210.8589078/13/20 07:05 AM8/13/20 03:02 PM
128/10/20 03:08 PM8907Clock OUT96.65.210.8589078/14/20 07:08 AM8/14/20 03:00 PM
138/11/20 05:08 PM6422Clock OUT96.65.210.8689078/17/20 07:06 AM8/17/20 03:11 PM
148/11/20 07:04 AM8907Clock IN96.65.210.8589078/18/20 07:02 AM8/18/20 03:03 PM
158/11/20 07:56 AM6422Clock OUT96.65.210.8589078/19/20 07:02 AM8/19/20 03:03 PM
168/11/20 07:56 AM6422Clock IN96.65.210.8589078/20/20 06:59 AM8/20/20 03:03 PM
178/11/20 03:09 PM8907Clock OUT96.65.210.8589078/24/20 07:09 AM8/24/20 03:01 PM
188/12/20 07:04 AM8907Clock IN96.65.210.8589078/25/20 07:07 AM8/25/20 03:01 PM
198/12/20 03:02 PM8907Clock OUT96.65.210.8589078/26/20 07:07 AM8/26/20 12:55 PM
208/13/20 07:05 AM8907Clock IN96.65.210.8589078/27/20 07:04 AM8/27/20 03:00 PM
218/13/20 03:02 PM8907Clock OUT96.65.210.8589078/28/20 07:08 AM 
228/14/20 07:08 AM8907Clock IN96.65.210.8589078/31/20 07:05 AM8/31/20 03:01 PM
238/14/20 03:00 PM8907Clock OUT96.65.210.8589079/1/20 07:08 AM9/1/20 02:59 PM
248/17/20 07:06 AM8907Clock IN96.65.210.8589079/2/20 07:05 AM9/2/20 03:00 PM
258/17/20 03:11 PM8907Clock OUT96.65.210.8589079/3/20 07:03 AM9/3/20 03:02 PM
268/18/20 07:02 AM8907Clock IN96.65.210.8589079/4/20 07:09 AM9/4/20 03:03 PM
278/18/20 03:03 PM8907Clock OUT96.65.210.8589079/7/20 07:05 AM9/7/20 03:01 PM
288/19/20 07:02 AM8907Clock IN96.65.210.8589079/8/20 07:04 AM9/8/20 03:11 PM
298/19/20 03:03 PM8907Clock OUT96.65.210.8589079/9/20 07:03 AM9/9/20 03:06 PM
308/20/20 06:59 AM8907Clock IN96.65.210.8589079/10/20 07:07 AM9/10/20 03:01 PM
318/20/20 03:03 PM8907Clock OUT96.65.210.8589079/11/20 07:09 AM 
328/21/20 03:03 PM8907Clock OUT96.65.210.85   
338/24/20 07:09 AM8907Clock IN96.65.210.85   
348/24/20 03:01 PM8907Clock OUT96.65.210.85   
358/25/20 07:07 AM8907Clock IN96.65.210.85   
368/25/20 03:01 PM8907Clock OUT96.65.210.85   
378/26/20 07:07 AM8907Clock IN96.65.210.85   
388/26/20 12:55 PM8907Clock OUT96.65.210.85   
398/27/20 07:04 AM8907Clock IN96.65.210.85   
408/27/20 03:00 PM8907Clock OUT96.65.210.85   
418/28/20 07:08 AM8907Clock IN96.65.210.85   
428/31/20 07:05 AM8907Clock IN96.65.210.85   
438/31/20 03:01 PM8907Clock OUT96.65.210.85   
449/1/20 07:08 AM8907Clock IN96.65.210.85   
459/1/20 02:59 PM8907Clock OUT96.65.210.85   
469/2/20 07:05 AM8907Clock IN96.65.210.85   
479/2/20 03:00 PM8907Clock OUT96.65.210.85   
489/3/20 07:03 AM8907Clock IN96.65.210.85   
499/3/20 03:02 PM8907Clock OUT96.65.210.85   
509/4/20 07:09 AM8907Clock IN96.65.210.85   
519/4/20 03:03 PM8907Clock OUT96.65.210.85   
529/7/20 07:05 AM8907Clock IN96.65.210.85   
539/7/20 03:01 PM8907Clock OUT96.65.210.85   
549/8/20 07:04 AM8907Clock IN96.65.210.85   
559/8/20 03:11 PM8907Clock OUT96.65.210.85   
569/9/20 07:03 AM8907Clock IN96.65.210.85   
579/9/20 03:06 PM8907Clock OUT96.65.210.85   
589/10/20 07:07 AM8907Clock IN96.65.210.85   
599/10/20 03:01 PM8907Clock OUT96.65.210.85   
609/11/20 07:09 AM8907Clock IN96.65.210.85   
619/11/20 03:01 PM8907Clock OUT96.65.210.85   
Sheet14
Cell Formulas
RangeFormula
H2:H61H2=IF(I2="","",IFERROR(VLOOKUP(I2,$A$2:$B$10000,2,0),""))
I2:I61I2=IF(ROWS(I$2:I2)>COUNTIF($C$2:$C$10000,$L$1),"",MINIFS($A$2:$A$10000,$A$2:$A$10000,">"&N(I1),$C$2:$C$10000,$L$1))
J2:J61J2=IF(ROWS(J$2:J2)>COUNTIF($C$2:$C$10000,$L$1),"",IFERROR(1/(1/MINIFS($A$2:$A$10000,$A$2:$A$10000,">"&I2,$B$2:$B$10000,H2,$C$2:$C$10000,$L$2,$A$2:$A$10000,"<"&MINIFS($I$2:$I$10000,$H$2:$H$10000,H2,$I$2:$I$10000,">"&I2))),""))
 

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Darn, I've used it on two different versions of excel. I swear I'm copying you formula exactly.
I'll have to play with it.
I really appreciate your help with this
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,846
Office Version
  1. 2019
Platform
  1. Windows
If your calculation is on auto then there must be an anomaly with the data that doesn't show up in the XL2BB grab. Your capture in post 5 and mine in post 8 are 100% identical in terms of source data and formulas, so the results should be identical as well. I'm as confused as you are as to why they are different. (formats are slightly different but that would not affect the formulas).

What do you get if you copy post 5 back to a blank sheet? (Click the clipboard icon in the top left corner of the XL2BB capture, then paste to A1 in excel).
Try the same with post 8. Do they both show the same results in column I, either correct or incorrect?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,081
Messages
5,526,729
Members
409,717
Latest member
Oscarsalone

This Week's Hot Topics

Top