Page 3 of 3 FirstFirst 1 2 3
Results 21 to 30 of 30

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

  1. #21
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,203
    Articles
    0
    Excel Version
    Office 365 Subscription


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  2. #22
    Acolyte Jym396's Avatar
    Join Date
    Jul 2017
    Location
    Monroeville, PA
    Posts
    23
    Articles
    0

    RE:

    Quote Originally Posted by AliGW View Post
    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.
    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.Example02.xlsm
    Attached Files Attached Files
    There are only 10 types of people in the world...those who understand binary..and those that don't.

  3. #23
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,203
    Articles
    0
    Excel Version
    Office 365 Subscription
    That's really clear now - thanks. Am looking now.
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #24
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,203
    Articles
    0
    Excel Version
    Office 365 Subscription
    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 by AliGW; 2017-07-25 at 05:27 PM.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #25
    Acolyte Jym396's Avatar
    Join Date
    Jul 2017
    Location
    Monroeville, PA
    Posts
    23
    Articles
    0
    Quote Originally Posted by AliGW View Post
    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)

    ????????????????????????????????
    Geez...I am sooo sorry... Cell K209
    There are only 10 types of people in the world...those who understand binary..and those that don't.

  6. #26
    Acolyte Jym396's Avatar
    Join Date
    Jul 2017
    Location
    Monroeville, PA
    Posts
    23
    Articles
    0
    Holy maceral...NOT K209...K208...geez Jim <lack of sleep>
    There are only 10 types of people in the world...those who understand binary..and those that don't.

  7. #27
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,203
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  8. #28
    Acolyte Jym396's Avatar
    Join Date
    Jul 2017
    Location
    Monroeville, PA
    Posts
    23
    Articles
    0

    Re: Index

    Quote Originally Posted by AliGW View Post
    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.
    IT WORKS!!!! YOU'RE A GODDESS!!! Tried INDEX( MATCH(...but I crashed. Also tried INDIRECT, VLOOKUP, =BANGMYHEADOFFTHEWALL.
    Thank you...Thank you so much!!!

    -Jim
    There are only 10 types of people in the world...those who understand binary..and those that don't.

  9. #29
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,203
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  10. #30
    Acolyte Jym396's Avatar
    Join Date
    Jul 2017
    Location
    Monroeville, PA
    Posts
    23
    Articles
    0
    Thank you...will do
    There are only 10 types of people in the world...those who understand binary..and those that don't.

Page 3 of 3 FirstFirst 1 2 3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •