I am trying to create a workbook that works out the number of overtime hours
worked by staff each month
In 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 H4 I
have =TIME(INT(B4),MOD(B4,1)*100,0)*24*C4*2*D4.
The *2 represents double time.
If I put the following in cell E4 =weekday(A4). What type of formula do I
need to add to H4 to multiply by either time and a half or double time. I now
know that the weekday 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. Is this an if statement how do I go about this.

Any help please.


Monty

Try:


Using weekday(a1,2) returns monday =1, sunday=7 so 5 i.e. 6,7 gives double
time *2) otherwise time and half (*1.5). No need to put anything in E1

=IF(WEEKDAY(A1,2)5,TIME(INT(B4),MOD(B4,1)*100,0)* 24*C4*2*D4,TIME(INT(B4),MOD(B4,1)*100,0)*24*C4*1.5 *D4)

HTH

"Monty" wrote:

I am trying to create a workbook that works out the number of overtime hours
worked by staff each month
In 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 H4 I
have =TIME(INT(B4),MOD(B4,1)*100,0)*24*C4*2*D4.
The *2 represents double time.
If I put the following in cell E4 =weekday(A4). What type of formula do I
need to add to H4 to multiply by either time and a half or double time. I now
know that the weekday 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. Is this an if statement how do I go about this.

Any help please.


Monty