overtime charging question

[Guest]

A B C
date
hours worked 9
name hours offered 10
hours charged 3

Hi I have a sheet set up this way and want to chart overtime. Anything over
8 hours is time and a half, Saturday's are time and a half all day and Sunday
is double charge.How can i set this up so it recognizes the date and charges
correctly on the weekends. Will Excel recognize the date as weekend? The
example above shows someone working 9 but offered 10 so charged 1 1/2 x 2 or
3 hours charged. Thanks Karl

 

[Gord Dibben]

Karl

Chip Pearson has a good site with examples and instructions for computing time
worked and overtime.

Also an example workbook to download.

Not sure if he addresses weekends or not, but have a look.

http://www.cpearson.com/excel/overtime.htm

Gord Dibben Excel MVP

 

[Myrna Larson]

The WEEKDAY function can be used to determine the day of the week.

 

[Dave O]

The WEEKDAY() function will return a number corresponding to the day
of the week for a given date. For instance: =WEEKDAY(11/2/2004)
returns 3 for Tuesday (where Sunday = day 1 of the week). This
function combined with some IF() statements will track your overtime.
(Note 11/2/2004 represents November 2 in this example.)

 

[Alex Delamain]

Column A = Date
Column B = Name
Column C = Offered Hours
Column D = Hours Worked
Column
=IF(WEEKDAY(A2,2)=7,MAX(C22)*2,IF(WEEKDAY(A2,2)=6,MAX(C22)*1.5,IF(OR(C2>8,D2>8),(MAX(C22)-8)*1.5+8,+D2)))

If the offered hours are greater than the hours worked the offere
hours are used to calculate the charge