How to Calculate Number of Overtime Hours but Only for Weekday

Hello,

I need help with a formula that will calculate the number of overtime hours, but only for weekdays.

I have in:
Column A1: Date of Work (for example, December 24, 2012)
Column A2: Number of Hours worked (for example, 8)
Column A3: Day of the Week (for example, Monday)

I want to put the formula in Column A4, which will show "Number of Overtime Hours (Weekdays)".
Basically, anything over 8 hours will be overtime. So in Column A4, I want something that says if A2>8 and A3 is Monday-Friday, then A2-8.
So lets say for example it is 12 hours on a Tuesday. I'm guessing the formula would be something like: =IF(A2>8 AND MONDAY-FRI???, (A2)-8,0)=4.

Can anyone help? Thanks,

Tony

try this in the 4th column:

Formula:

Please Login or Register  to view this content.

The weekday formula computes the day for a date, with Mon=1, Tue=2, .... Sat=6 and Sun=7. Since you only need from 1 to 5, using "<6" would get all those values. Hope that helps!