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

Hi,

I have no idea what you mean by a formula that will work outside Excel but
this will work in Excel

=((B1-A1)-C1)*24

Mike

"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

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

Hi Pol
If you use a colon separator for standard time notation (ex. 9:00) rather
than a decimal point (9.00) the following will give you a result in a HH:MM
format:
=B1-A1-C1
With cell A containing the start time, B the finish time and C the break time.
Start Finish Break Hours
9:00 17:30 1:40 6:50
Note: If the start time is before midnight and the finish time is after,
14:00 to 1:00 (AM), the finish time must be entered as-in the example-25:00.

Mike's formula results in a decimal number for easy calculation when
multiplied by hourly compensation, but you still need to use the colon format
(9:00) to make it work.

"Mike H" wrote:

Hi,

I have no idea what you mean by a formula that will work outside Excel but
this will work in Excel

=((B1-A1)-C1)*24

Mike

"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