+ Reply to Thread
Results 1 to 3 of 3

Needing to combine 2 IF statements in one Cell. Time Schedule minus lunch time

  1. #1
    Registered User
    Join Date
    10-16-2010
    Location
    chicago, il
    MS-Off Ver
    Excel 2003
    Posts
    4

    Needing to combine 2 IF statements in one Cell. Time Schedule minus lunch time

    I am creating a work schedule and have 3 columns; one for Start Time (A2), one for Stop Time (C2) and one for Hours worked (D2).

    In the Stop Time column i am needing the option to put in the word "Close" to represent a defined time (lets say 6 PM) instead of typing in an actual time like 6:00 PM.

    The following formula is what im using now and it works perfectly!

    =SUM(IF(C2="CLOSE","18:00",C2)-IF(A2="","0",A2)+(C2<A2))*24
    ^
    ^
    used to calculate time if after midnight


    However, I am now needing to take out 30 mins for lunch if the resulting sum of the above formula is = or > 5.

    I Know the formula for taking out the 30 mins required is: =(IF(C2*24-A2*24=>5,C2*24-A2*24,C2*24-A2*24-.5)).

    But how do i..or is it possible to have both of these IF statments in the same cell (D2) with C2 keeping the value of "18:00" if it is populated with "Close"? Or is there an easier way to subtract the 30 mins from my original formula if the sum is over 5 hours?

    Thanks in advance for any assistance!
    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Needing to combine 2 IF statements in one Cell. Time Schedule minus lunch time

    maybe..

    =SUM(IF(C2="CLOSE","18:00",C2)-IF(A2="","0",A2)+(C2<A2))*24-(0.5*(SUM(IF(C2="CLOSE","18:00",C2)-IF(A2="","0",A2)+(C2<A2))*24<=5))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    10-16-2010
    Location
    chicago, il
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Needing to combine 2 IF statements in one Cell. Time Schedule minus lunch time

    That's perfect Ace_XL!! had to change the < sign to a > at the end but thats what i needed! Thanks so much for your expertise!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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