complicated excel formulas

[Guest]

I created a time sheet which contains the following field: Time In (column
c), Time Out (column d), Time In (column e), Time Out (column f). The second
time in and out is for lunch out and lunch in.

The next step is calculate the hours. There are 5 columns. First column to
calculate up to 8 hrs, second column is anything betwen 9 and 12 hours, third
column is 12+ columns. Each of these sets assume a deduction for lunch (the
second time in and time out).

Now, the problem - the fourth column, assuming that no lunch but my
calculation is not working. Here's the formula
if(d12="",0)*AND(e12="",0),round(f14-c14)*24,2). Any help would be
appreciated.

 

[Guest]

Hi Dan,

I have become confused.

Can you provide a sample of real data and the answers that your expect to
get someting like this showing what cells the data will be in:-
Start time: C12=8am
Lunch start: D12=12noon
Lunch finish: E12=1pm
Finish for day: F12=7pm

G12 should =
H12 should =
I12 should =

Am I correct in assuming the following?:-
8 hrs is standard time

8 hrs to 12 is overtime at say rate1
12 hrs is overtime at say rate2

Should the sum of the above equal total hrs for the day?

Also are you entering times as formatted times?

Regards,

OssieMac

 

[David Biddulph]

Well you haven't made it clear what you want your formula to do, but we can
look at what you've got, and analyse each function at a time.
The first thing you need to do is make sure that your parentheses match,
which yours don't; you've got 3 opening and 4 closing so you need to sort
out where you've lost one.
The first function you've got is if(d12="",0). That will return zero if D12
is the empty string "", and will default to the logical value FALSE for any
other value of D12 as you haven't given an alternative.
You've then multiplied that IF result by AND(e12="",0). AND will take 0 as
being the equivalent of a logical FALSE, so whatever result you have for the
E12 test, the AND will return FALSE.
You are multiplying something that is either zero or FALSE by something
which is always FALSE. The multiplication result will always be zero.
You've then got a comma, which isn't valid syntax at this stage, so it
presumably implies that you wanted something different somewhere else (and
may be related to your mismatched parentheseses or to your missing
alternative from the IF function?). It therefore isn't clear how the IF*AND
multiplication result should be combined with the final ROUND output.
The final function you've got is is round(f14-c14)*24,2). Your parentheses
here don't match, but you may perhaps have intended round((f14-c14)*24,2),
which would multiply (f14-c14) by 24 (perhaps to convert Excel time to
decimal hours?), and then round that to 2 decimal places, so 5:40 in
(f14-c14) would give a resuly of 5.67 from that function.

When you've decided what you want the formula to do, you can correct it
accordingly. If you don't know the syntax or the operation of any Excel
function, look it up in Excel help, where it gives examples and often gives
a "See also" link to related functions.