Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formula
I have done a excel work sheet to work out my wage but I am just stuck on one
thing. When I work longer than 8 hours I get an hours dinner brake unpaid. Im know wanting to know if there is a formula that works it out. In Cell B2 I have the time I started and in C2 the time I finish. In cell D2 I have the formula =SUM(C2)-(B2) that equals the total amount off hours I work. Well now I want it to automatically take 1 off if the hours are 8 or over 8. |
#2
|
|||
|
|||
Hi
Try: =IF(C2-B2=8,C2-B2-1,C2-B2) HTH Michael Mitchelson "ste997" wrote: I have done a excel work sheet to work out my wage but I am just stuck on one thing. When I work longer than 8 hours I get an hours dinner brake unpaid. Im know wanting to know if there is a formula that works it out. In Cell B2 I have the time I started and in C2 the time I finish. In cell D2 I have the formula =SUM(C2)-(B2) that equals the total amount off hours I work. Well now I want it to automatically take 1 off if the hours are 8 or over 8. |
#3
|
|||
|
|||
Note that the SUM function in your formula is not necessary.
=C2-B2 will work just as well. Assuming that when you say 8 hours you mean 8 hours (ie start work at 8am and work until 4:30pm which is 8 1/2hours, so you are intitled to 1 hour unpaid dinner break. Which means that you work 7 1/2 hours and have 1 hour dinner), then if you are using real numbers and not Excel times =C2-C1-(C2-C18) will give you what you want. The -(C2-C18) will produce a TRUE/FALSE Boolean which because it is subtracted, Excel will convert to a 1 for TRUE or 0 for FALSE. If you are using Excel times (8:00, 17:00 etc) then =(C2-C1)-((C2-C11/3)/24) Excel times are fractions of (one) day so 8 hours is one day divided by 3 thus 1/3. This will again produce a TRUE/FALSE Boolean which will, when it is converted to a number, need to be divided by 24 (hours in a day) to give the correct 1 hour (1:00) subtraction. If you really meant 9 hours, (ie 8 hours actual work + 1 hour dinner) then use 0.375 (9/24) instead of the 1/3 If you meant 8 hours OR more then use = in place of simply in the formula -- HTH Sandy Replace@mailinator with @tiscali.co.uk "ste997" wrote in message ... I have done a excel work sheet to work out my wage but I am just stuck on one thing. When I work longer than 8 hours I get an hours dinner brake unpaid. I'm know wanting to know if there is a formula that works it out. In Cell B2 I have the time I started and in C2 the time I finish. In cell D2 I have the formula =SUM(C2)-(B2) that equals the total amount off hours I work. Well now I want it to automatically take 1 off if the hours are 8 or over 8. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |