On Dec 8, 9:06*pm, pol wrote:
Hi all,

Please anybody can help me to write a general function that can be used in
excel as well as outside excel. I have the following information

opening time * * * * closing time * *breake * * * * Effective hrs
9.00 * * * * * * * * * * *17.30 * * * * * *1.40 * * * * * *?
8.30 * * * * * * * * * * *16.40 * * * * * *1.50 * * * * * *?
7.40 * * * * * * * * * * *17.50 * * * * * *2.45 * * * * * *?

I want to write a function to find effective hours. Pls help

With thanks

Polachan

Pol,

First ..... background:
Excel stores dates as the number of days from either jan 1 1904 or jan
1 1900, depending on setup. Assume you, like most people are using the
1900 setup. 1/1/1900 is day 1, 2/1/190 is day 2, 1/1/1901is day 366
and so on. These day values are called date serials.
Times are stored as a fraction of a day, i.e noon is .5, 6pm is .75
8am is, .333333. so 0900 is .375. To enter times, use the ":"
separator, and Excel will automatically recognise the entry as a time
value.

This makes date/time calculations really easy, with one drawback a
date/time value can never be negative.

Assume your data is columns A, B and C, with the formula in D, first
data row at row 2.
Cell D2 contains the formula "=B2-A2-C2" and displays the result
"6:50" (remember to use the : separator.
Copy down.

This will only work if the times don't go over midnight. If that is
the case, you need to use a slightly more complicated version, and
incorporate the IF function.

try "=If( A2B2, 1+B2-A2-C2, B2-A2-C2)"

the if function has three parts:
Logical Test (in this case A2B2) - the Logical test must evaluate to
either true or false
Value if true (1+B2-A2-C2) - this is the formula for when the logical
test = true
Value if false (the rest) - when logical test = false

Each part is separated by the ","

In this If function if A2 is greater than B2 (start is after finish)
then we add one to the formula to calculate the time.

Assume start 21:00, finish 03:00, elapsed time 6:00, however Excel
would calculate this as .125 (B2) - .875 (A2) and end up with
negative .75 BUT Excel cannot recognise the negative time, and
therefore produces an error message.
By adding 1 to the formula, we effectively make the calculation 1.125-.
875 result .25 displayed as "6:00"

HTH

Steve