if?

[Guest]

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

 

[Guest]

Typo: should be A4 not A1.

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

 

[Guest]

thanks, worked a treat.

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

 

[Guest]

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