Which formula is correct for calculating times?

I built a spreadsheet to calculate hours worked; I did it over a couple of weeks, reading and learning as I went. During the time I was doing this, somehow I ended up with 2 different formula for calculating hours worked...these are:

=IF(N31>O31,MIN(("24:00"-N31+O31)*24,7.25),MIN((O31-N31)*24,7.25))

and

=IF(N32>O32,CEILING(("24:00"-N32+O32),7.25),MIN((O32-N32)*24,7.25))

where N and O are the start & finsh times respectively.

Both seem to calculate properly, but does anyone know which is the better formula to use...of does it matter ? Thanks.

Hi!

They both seem to work (not exhaustively tested) but give this a try:

=MIN((O31-N31+(O31<N31))*24,7.25)

Biff

"djm" <djm.27gm1m_1147042502.264@excelforum-nospam.com> wrote in message
news:djm.27gm1m_1147042502.264@excelforum-nospam.com...
>
> I built a spreadsheet to calculate hours worked; I did it over a couple
> of weeks, reading and learning as I went. During the time I was doing
> this, somehow I ended up with 2 different formula for calculating hours
> worked...these are:
>
> =IF(N31>O31,MIN(("24:00"-N31+O31)*24,7.25),MIN((O31-N31)*24,7.25))
>
> and
>
> =IF(N32>O32,CEILING(("24:00"-N32+O32),7.25),MIN((O32-N32)*24,7.25))
>
> where N and O are the start & finsh times respectively.
>
> Both seem to calculate properly, but does anyone know which is the
> better formula to use...of does it matter ? Thanks.
>
>
> --
> djm
> ------------------------------------------------------------------------
> djm's Profile:
> http://www.excelforum.com/member.php...fo&userid=4793
> View this thread: http://www.excelforum.com/showthread...hreadid=539705
>

....can you tell me the difference b/w the 2 formulas that I have in my spreadsheet..in terms of the way that they work...and how do they compare to yours ?.....or are all 3 different, but equally valid ways of doing the same thing?

Originally Posted by djm

I built a spreadsheet to calculate hours worked; I did it over a couple of weeks, reading and learning as I went. During the time I was doing this, somehow I ended up with 2 different formula for calculating hours worked...these are:

=IF(N31>O31,MIN(("24:00"-N31+O31)*24,7.25),MIN((O31-N31)*24,7.25))

and

=IF(N32>O32,CEILING(("24:00"-N32+O32),7.25),MIN((O32-N32)*24,7.25))

where N and O are the start & finsh times respectively.

Both seem to calculate properly, but does anyone know which is the better formula to use...of does it matter ? Thanks.

CEILING won't do the same as MIN so they won't always give the same result. try start time 22:00 and end time 05:00.....

What's the intent? If you want the differnece between the two times but not to exceed 7.25 hours then use Biff's suggestion or this which does the same

=MIN(MOD(O31-N31,1)*24,7.25)