How to calculate no. of hours under different circumstances.
 

3 situations would be as follow, i set the formular like this is =IF(B1<A1,
24/24-A6+B6,B6-A6), both A & B using time format and C using number format.

A B C
Start Time End Time No. of hours
10:30 15:00 4.5
00:00 08:00 8.0
08:00 23:59 16.0 **Problem**

I can get the result for the first 2, but not the last one, any one can
help, many thanks!

How to calculate no. of hours under different circumstances.
 

What's the problem?

4.5
8
15.98333


is what I get using

=(B1-A1+(A1B1))*24

are you saying that you want to round, if so what's the rounding criteria?
Nearest .25 using decimal hours? If so

=ROUND((B1-A1+(A1B1))*24/0.25,0)*0.25

will return 16



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"evon" wrote in message
...
3 situations would be as follow, i set the formular like this is =IF(B1<A1,
24/24-A6+B6,B6-A6), both A & B using time format and C using number
format.

A B C
Start Time End Time No. of hours
10:30 15:00 4.5
00:00 08:00 8.0
08:00 23:59 16.0 **Problem**

I can get the result for the first 2, but not the last one, any one can
help, many thanks!

How to calculate no. of hours under different circumstances.
 

Hi!

Try this:

=(B6-A6+(B6<A6))*24

Copy down as needed.

Returns:

4.5
8
15.9833333333333

Biff

"evon" wrote in message
...
3 situations would be as follow, i set the formular like this is =IF(B1<A1,
24/24-A6+B6,B6-A6), both A & B using time format and C using number
format.

A B C
Start Time End Time No. of hours
10:30 15:00 4.5
00:00 08:00 8.0
08:00 23:59 16.0 **Problem**

I can get the result for the first 2, but not the last one, any one can
help, many thanks!

How to calculate no. of hours under different circumstances.
 

Yup, that is what i am looking for the rounding problem, and it works
perfectly would you explain a little bit the logic behind, many thanks!

Regards,
evon

"Peo Sjoblom" wrote:

What's the problem?

4.5
8
15.98333


is what I get using

=(B1-A1+(A1B1))*24

are you saying that you want to round, if so what's the rounding criteria?
Nearest .25 using decimal hours? If so

=ROUND((B1-A1+(A1B1))*24/0.25,0)*0.25

will return 16



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"evon" wrote in message
...
3 situations would be as follow, i set the formular like this is =IF(B1<A1,
24/24-A6+B6,B6-A6), both A & B using time format and C using number
format.

A B C
Start Time End Time No. of hours
10:30 15:00 4.5
00:00 08:00 8.0
08:00 23:59 16.0 **Problem**

I can get the result for the first 2, but not the last one, any one can
help, many thanks!