+ Reply to Thread
Results 1 to 3 of 3

Conditional specific time frame calculation

  1. #1
    Forum Contributor
    Join Date
    04-18-2011
    Location
    Jeddah
    MS-Off Ver
    Excel 2007
    Posts
    362

    Post Conditional specific time frame calculation

    The formula in T3 from the attached sheet is working fine, it is calculating the night working hours for the time frame between 18:00 – 06:00 if it happens to fall in the times values of J3 and L3.

    I would like please for the existing formula to return blank IF none of the following criteria values is in B3; which can be selected from the B3 dropdown menu.
    "PRG/FLY",B3="FLY1",B3="FLY",B3="PRG/FLY-CO",B3="FLY1-CO",B3="FLY-CO"

    Reference to the attached sheet, the expected formula should return please 1:06, but since there is a “TVL” in B3 which not one of the criteria values listed above, it should return blank instead.

    I tried the following modification, but unfortunately the modified formula has a problem.

    =IFERROR(1/(1/IF(AND(OR(B3="PRG/FLY",B3="FLY1",B3="FLY",B3="PRG/FLY-CO",B3="FLY1-CO",B3="FLY-CO"),INT(L3),MIN(6/24,MOD(L3,1))-MIN(6/24,MOD(J3,1))+MAX(MOD(L3,1),18/24)-MAX(18/24,MOD(J3,1)),MIN(INT(L3)+6/24,L3)-MAX(INT(J3)+18/24,J3))),"")

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,147

    Re: Conditional specific time frame calculation

    Try

    =IFERROR(1/(1/IF(AND(OR(B3="PRG/FLY",B3="FLY1",B3="FLY",B3="PRG/FLY-CO",B3="FLY1-CO",B3="FLY-CO")),IF(INT(J4)=INT(L4),MIN(6/24,MOD(L4,1))-MIN(6/24,MOD(J4,1))+MAX(MOD(L4,1),18/24)-MAX(18/24,MOD(J4,1)),MIN(INT(L4)+6/24,L4)-MAX(INT(J4)+18/24,J4)),"x")),"")

  3. #3
    Forum Contributor
    Join Date
    04-18-2011
    Location
    Jeddah
    MS-Off Ver
    Excel 2007
    Posts
    362

    Re: Conditional specific time frame calculation

    Thank you,

    The formula is working fine in T3 after replacing J4 and L4 with J3 and L3.

+ 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. [SOLVED] Conditional Formatting: Highlight specific time frame
    By dlealb in forum Excel General
    Replies: 1
    Last Post: 02-23-2020, 01:30 AM
  2. [SOLVED] Sum between dates for a specific column and specific time frame
    By excel_tmd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2019, 03:10 PM
  3. [SOLVED] time line adding a specific time frame in each additional cell
    By RobertM01 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-23-2014, 10:22 AM
  4. Sum number of calls within a specific time frame
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2013, 11:38 PM
  5. [SOLVED] Assigning a category to a specific time frame
    By Woofaloo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-18-2013, 07:15 PM
  6. Excel 2007 : Adds values for a specific time frame
    By Raist23 in forum Excel General
    Replies: 3
    Last Post: 03-15-2012, 05:43 AM
  7. Number of tasks completed in a specific time frame
    By Robert in forum Excel General
    Replies: 2
    Last Post: 02-03-2006, 09:30 AM

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