Results 1 to 3 of 3

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

Threaded View

  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

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