+ Reply to Thread
Results 1 to 8 of 8

Fomula to return diff results based on criteria?

  1. #1
    Registered User
    Join Date
    04-15-2021
    Location
    Adelaide Australia
    MS-Off Ver
    365 MSO 64 bit
    Posts
    4

    Smile Fomula to return diff results based on criteria?

    Hello All

    I am trying to figure out which formula to use in the highlighted part of attached workbook.

    In Green box - hours actually worked
    In Purple Box - contracted normal hours.

    In the RED box - is the difference between contracted hours and hours actually worked. (extra hours worked) - I think I have this right. But need it to also reflect 0:00:00 if Green & purple both say 76:00:00. i get a multitude of ###### at the moment if these match.

    In the BLUE box are the hours (OVER max 10 hrs pre approved) - so in example given, I need this to reflect 7:12:00 as 10 hours of the 17:12:00 are pre-approved and forms for the 7:12:00 must be supplied.

    However - at same time if contracted hours and hours actually worked are both 76:00:00 - I need this also to reflect 0:00:00

    I am struggling! Thanks for any assistance, much appreciated.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Fomula to return diff results based on criteria?

    You dont need to use the SUM function when adding (or subtracting) 2 cells/numbers, you can just + them together (your way is not wrong, just a bit redundant)
    =SUM(J25,J45)
    =J25+J45

    =SUM(Q12-Q13)
    =Q12-Q13

    Try this for the RED...
    =if(Q12=Q13,0,Q12-Q13)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-15-2021
    Location
    Adelaide Australia
    MS-Off Ver
    365 MSO 64 bit
    Posts
    4

    Re: Fomula to return diff results based on criteria?

    ha! yeah I am self taught and a bit old school

    Thanks very much! that worked.

    Would you please have any ideas for the blue cell?

    Thanks again.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Fomula to return diff results based on criteria?

    Try this...
    =IF(Q13=Q12,0,Q14-10/24)

  5. #5
    Registered User
    Join Date
    04-15-2021
    Location
    Adelaide Australia
    MS-Off Ver
    365 MSO 64 bit
    Posts
    4

    Re: Fomula to return diff results based on criteria?

    Thank you that has worked - however I just need to get it to read as a zero if RED sum is less than the 10:00:00 rather than blue showing as negative hours.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Fomula to return diff results based on criteria?

    Maybe something like...
    =IF(cell<=10/24,0,formula)

  7. #7
    Registered User
    Join Date
    04-15-2021
    Location
    Adelaide Australia
    MS-Off Ver
    365 MSO 64 bit
    Posts
    4

    Re: Fomula to return diff results based on criteria?

    got it!
    I was even able to figure out what "cell" and "formula" should be, all by myself :D
    Thanks so much! I really appreciate all your help!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Fomula to return diff results based on criteria?

    Awesome!! It's always a great feeling when you figure out something like that yourself

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 02-17-2021, 09:18 AM
  2. [SOLVED] Filter multiple sheets based on 3 criteria and return results into single cell
    By matt2877 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-02-2021, 12:07 AM
  3. Return multiple results based on multi-criteria selection
    By Macpic in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 10-19-2017, 12:33 PM
  4. Return multiple results based on multiple criteria
    By sthomay in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-06-2017, 01:30 PM
  5. [SOLVED] Return multiple results when a criteria is met
    By kpodoh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2014, 11:49 AM
  6. Replies: 0
    Last Post: 07-30-2013, 07:42 PM
  7. Return results based on multiple criteria
    By Bryce in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-28-2005, 08:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1