Results 1 to 8 of 8

Nested IF statements in relation to dates

Threaded View

  1. #1
    Registered User
    Join Date
    09-16-2009
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2003
    Posts
    30

    Nested IF statements in relation to dates

    I’m somewhat new to formulas in Excel so I’ll try and explain my problem as best as I can. I have a time sheet that is (more or less) setup as follow:
    Columns A thru F are titled
    A – Date (format: Custom/[$-409]ddd, m/dd/yy to show Mon, 6/01/09)
    B - Time In (format: Time/1:30 PM)
    C - Time Out (format: Time/1:30 PM)
    D - Total Hours (format: Custom/h:mm)
    E – Is a hidden column (formatted as a general number)
    F – Total Hour over/under (format: Custom/h:mm)
    Under Tools/Options/Calculations, I’ve checked “1904 date system” so that negative times would show.
    The cells in column D use formula “=C2-B2”
    The hidden E column uses formula “=((D2)*24)-8”
    Column F uses formula “=IF(D2=0,0,E2/24)

    With the way this sheet is setup as stated above, it does just what I want it to do with the exception of one more variable.
    Any hours worked on a weekend day (Sat or Sun) are all overtime hours. So, I need to add something to the formula so that when hours are entered on a weekend day, column F only shows those total overtime hours. I need for no negative numbers to show on a weekend date.
    (On the regular week days, if someone only worked 4 hours, 4:00 will show in column D and a -4:00 will show in column F. On a Sat or Sun, if an employee works from 8am to 12PM, I want columns D and F to show 4:00).

    (see attached file)

    I hope this makes sense and I hope someone has a solution.

    Thanks, Kirk
    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