Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 a =IF(N31O31,MIN(("24:00"-N31+O31)*24,7.25),MIN((O31-N31)*24,7.25)) and =IF(N32O32,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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Which formula is correct for calculating times?
Hi!
They both seem to work (not exhaustively tested) but give this a try: =MIN((O31-N31+(O31<N31))*24,7.25) Biff "djm" wrote in message ... 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 a =IF(N31O31,MIN(("24:00"-N31+O31)*24,7.25),MIN((O31-N31)*24,7.25)) and =IF(N32O32,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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Which formula is correct for calculating times?
....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? -- djm ------------------------------------------------------------------------ djm's Profile: http://www.excelforum.com/member.php...fo&userid=4793 View this thread: http://www.excelforum.com/showthread...hreadid=539705 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Which formula is correct for calculating times?
djm Wrote: 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 a =IF(N31O31,MIN(("24:00"-N31+O31)*24,7.25),MIN((O31-N31)*24,7.25)) and =IF(N32O32,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) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=539705 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Formula to average certain times | Excel Discussion (Misc queries) | |||
Formula to Average times...again | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula to calculate elapsed time between certain dates and times | Excel Discussion (Misc queries) |