software

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

Typo: should be A4 not A1.

quot;Toppersquot; wrote:

gt; Try:
gt;
gt;
gt; Using weekday(a1,2) returns monday =1, sunday=7 so gt;5 i.e. 6,7 gives double
gt; time *2) otherwise time and half (*1.5). No need to put anything in E1
gt;
gt; =IF(WEEKDAY(A1,2)gt;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)
gt;
gt; HTH
gt;
gt; quot;Montyquot; wrote:
gt;
gt; gt; I am trying to create a workbook that works out the number of overtime hours
gt; gt; worked by staff each month
gt; gt; In Cell A4 I input the date, cell B4 I input the hours,
gt; gt; C4 the hourly rate is input, D4 the number of staff is input , in cell H4 I
gt; gt; have =TIME(INT(B4),MOD(B4,1)*100,0)*24*C4*2*D4.
gt; gt; The *2 represents double time.
gt; gt; If I put the following in cell E4 =weekday(A4). What type of formula do I
gt; gt; need to add to H4 to multiply by either time and a half or double time. I now
gt; gt; know that the weekday returns a number between 1 and 7. Sunday being 1,
gt; gt; Monday 2, Tuesday 3 etc. So if it returns between 2 and 6 its a weekday and
gt; gt; if it is 1 or 7 it is weekend. Is this an if statement how do I go about this.
gt; gt;
gt; gt; Any help please.
gt; gt;
gt; gt;
gt; gt; Monty

thanks, worked a treat.

montyquot;Toppersquot; wrote:

gt; Try:
gt;
gt;
gt; Using weekday(a1,2) returns monday =1, sunday=7 so gt;5 i.e. 6,7 gives double
gt; time *2) otherwise time and half (*1.5). No need to put anything in E1
gt;
gt; =IF(WEEKDAY(A1,2)gt;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)
gt;
gt; HTH
gt;
gt; quot;Montyquot; wrote:
gt;
gt; gt; I am trying to create a workbook that works out the number of overtime hours
gt; gt; worked by staff each month
gt; gt; In Cell A4 I input the date, cell B4 I input the hours,
gt; gt; C4 the hourly rate is input, D4 the number of staff is input , in cell H4 I
gt; gt; have =TIME(INT(B4),MOD(B4,1)*100,0)*24*C4*2*D4.
gt; gt; The *2 represents double time.
gt; gt; If I put the following in cell E4 =weekday(A4). What type of formula do I
gt; gt; need to add to H4 to multiply by either time and a half or double time. I now
gt; gt; know that the weekday returns a number between 1 and 7. Sunday being 1,
gt; gt; Monday 2, Tuesday 3 etc. So if it returns between 2 and 6 its a weekday and
gt; gt; if it is 1 or 7 it is weekend. Is this an if statement how do I go about this.
gt; gt;
gt; gt; Any help please.
gt; gt;
gt; gt;
gt; gt; Monty

Try:Using weekday(a1,2) returns monday =1, sunday=7 so gt;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)gt;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

quot;Montyquot; wrote:

gt; I am trying to create a workbook that works out the number of overtime hours
gt; worked by staff each month
gt; In Cell A4 I input the date, cell B4 I input the hours,
gt; C4 the hourly rate is input, D4 the number of staff is input , in cell H4 I
gt; have =TIME(INT(B4),MOD(B4,1)*100,0)*24*C4*2*D4.
gt; The *2 represents double time.
gt; If I put the following in cell E4 =weekday(A4). What type of formula do I
gt; need to add to H4 to multiply by either time and a half or double time. I now
gt; know that the weekday returns a number between 1 and 7. Sunday being 1,
gt; Monday 2, Tuesday 3 etc. So if it returns between 2 and 6 its a weekday and
gt; if it is 1 or 7 it is weekend. Is this an if statement how do I go about this.
gt;
gt; Any help please.
gt;
gt;
gt; Monty