Why won't this work? I KNOW it's me...

I need more information, Jim. I know this spreadsheet is completely clear to you, but where is the data you want to analyse? I cannot work out where to find the hours worked for each category - what categories? - there's a lot going on here! You are going to need to explain one example step-by-step, including the expected outcome, for one set of data. If you don't, I can't advise you about the best way of doing it.
 
RE:

AliGW said:
I need more information, Jim. I know this spreadsheet is completely clear to you, but where is the data you want to analyse? I cannot work out where to find the hours worked for each category - what categories? - there's a lot going on here! You are going to need to explain one example step-by-step, including the expected outcome, for one set of data. If you don't, I can't advise you about the best way of doing it.
Click to expand...

OK...The categories I've changed to RED font. The hours worked to LIGHT BLUE.
If you look @cell G209 my MAX/IF statement is there and it correctly finds May 14 11.30 hours. I just want the date of that occurrence. 4 Columns to the left...same row. See example2.slxm.View attachment Example02.xlsm
 

Attachments

  • Example01.xlsm
    198 KB · Views: 12
Are you sure you have attached the correct workbook? I can see no red text at all and this is in G209: =IF(ISBLANK(F209),"",(F209-E209)*24)

????????????????????????????????

Excel 2016 (Windows) 32 bit
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
206​
​
​
#REF!​
207​
11​
21​
Date​
S​
In​
Out​
Hrs Day​
Hrs PP​
Sched​
208​
®​
​
Sunday, July 16, 2017​
9​
10:25 AM​
6:30 PM​
8.08​
​
-OFF-​
209​
​
=​
Monday, July 17, 2017​
K​
6:37 AM​
4:49 PM​
10.21​
10.21​
7-3|8​
210​
.​
=​
Tuesday, July 18, 2017​
K​
6:29 AM​
3:40 PM​
9.19​
19.40​
7-3|8​
211​
​
​
Wednesday, July 19, 2017​
O​
10:30 AM​
4:15 PM​
5.75​
​
- OFF-​
212​
​
!​
Thursday, July 20, 2017​
O​
11:05 AM​
11:45 AM​
0.67​
​
- OFF-​
213​
​
=​
Friday, July 21, 2017​
K​
6:39 AM​
4:56 PM​
10.28​
29.68​
7-3|8​
214​
​
=​
Saturday, July 22, 2017​
K​
6:37 AM​
4:04 PM​
9.44​
39.13​
7-3|8​
215​
​
=​
PP2 - Week 1 Totals​
##​
-0.88​
9.78​
39.12​
39.13​
#REF!​
Sheet: Timesheet
 
Last edited:
Try this entered as an array (C+S+E):

=INDEX($C$8:$C$284,MATCH(MAX(IF($D$8:$D$284=S1,$G$8:$G$284)),$G$8:$G$284,0))

It will work as long as there is only one maximum - if there are two or more that match, you'll get the earliest date it occurs.
 
Re: Index

AliGW said:
Try this entered as an array (C+S+E):

=INDEX($C$8:$C$284,MATCH(MAX(IF($D$8:$D$284=S1,$G$8:$G$284)),$G$8:$G$284,0))

It will work as long as there is only one maximum - if there are two or more that match, you'll get the earliest date it occurs.
Click to expand...

IT WORKS!!!! YOU'RE A GODDESS!!! :hail: Tried INDEX( MATCH(...but I crashed. Also tried INDIRECT, VLOOKUP, =BANGMYHEADOFFTHEWALL.
Thank you...Thank you so much!!!

-Jim
:popcorn:
 
No worries - I am so glad we got there before 6pm - that's my cut-off time, which is why I'm about to go offline. I will happily explain why OFFSET would not work in this case tomorrow, if you'd like. No time now, though. :)
 
You must log in or register to reply here.
Back
Top