#1   Report Post  
Posted to microsoft.public.excel.misc
Monty
 
Posts: n/a
Default time sheet

I want to work out the number of overtime hours worked by double time or time
and a half each month. Cell A4 I input the date, cell B4 I input the hours,
C4 the hourly rate is input, D4 the number of staff is input , in cell E4 I
have the total hours worked.
I have this in cell E4 =TIME(INT(B4),MOD(B4,1)*100,0)*24*C4*2*D4. as you can
see I have to input double time in the formula, staff also work time and a
half. Is there anyway I can get the formula to look at the date input and if
it is a weekday then it is time and a half and if it is a weekend then double
time or can I create a column for double time and one for time and a half and
if yes is input into either of these columns then it would do this
calculation.

Any help would be greatly appreciated

Monty

  #2   Report Post  
Posted to microsoft.public.excel.misc
Satraj
 
Posts: n/a
Default time sheet

You could use =weekday(A4) which returns a number between 1 and 7. Sunday
being 1, Monday 2, Tuesday 3 etc. So if it returns between 2 and 6 its a
weekday and if it is 1 or 7 it is weekend. Hope this helps.

"Monty" wrote:

I want to work out the number of overtime hours worked by double time or time
and a half each month. Cell A4 I input the date, cell B4 I input the hours,
C4 the hourly rate is input, D4 the number of staff is input , in cell E4 I
have the total hours worked.
I have this in cell E4 =TIME(INT(B4),MOD(B4,1)*100,0)*24*C4*2*D4. as you can
see I have to input double time in the formula, staff also work time and a
half. Is there anyway I can get the formula to look at the date input and if
it is a weekday then it is time and a half and if it is a weekend then double
time or can I create a column for double time and one for time and a half and
if yes is input into either of these columns then it would do this
calculation.

Any help would be greatly appreciated

Monty

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
time sheet drop down lists Steve Excel Discussion (Misc queries) 0 March 16th 06 10:45 PM
Time Sheet S.E. Excel Discussion (Misc queries) 2 October 14th 05 07:22 PM
Formula to deduct unpaid breaks in time sheet Rick Excel Discussion (Misc queries) 3 August 26th 05 11:53 PM
time sheet calculations emmcee Excel Worksheet Functions 7 August 20th 05 12:26 PM
Excel formula for a time sheet HRMSN Excel Worksheet Functions 1 August 10th 05 03:07 PM


All times are GMT +1. The time now is 01:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"