|
Help with Time Sheet
Hi, My name is Chuck and I am trying to write a spreadsheet to be used as a daily timesheet. The way it is set is: c6 is Time In, d6 Time out for Lunch, e6 Time in from lunch and f6 is Time Out. The formula I am using for total time is (d6-c6)+(f6-e6) I need to round this to the 1/4 hour. Any help is appreciated. Chuck -- CRobey4393 ------------------------------------------------------------------------ CRobey4393's Profile: http://www.excelforum.com/member.php...o&userid=28257 View this thread: http://www.excelforum.com/showthread...hreadid=478463 |
Help with Time Sheet
=ROUND(((D6-C6)+(F6-E6))*96,0)/4
Times in Excel are just fractions of a day, so you'd normally multiply by 24 to convert a time (or a time difference) to a number of hours. Multiply by another 4, round, and divide by 4 to get the nearest quarter-hour. HTH. --Bruce "CRobey4393" wrote: Hi, My name is Chuck and I am trying to write a spreadsheet to be used as a daily timesheet. The way it is set is: c6 is Time In, d6 Time out for Lunch, e6 Time in from lunch and f6 is Time Out. The formula I am using for total time is (d6-c6)+(f6-e6) I need to round this to the 1/4 hour. Any help is appreciated. Chuck -- CRobey4393 ------------------------------------------------------------------------ CRobey4393's Profile: http://www.excelforum.com/member.php...o&userid=28257 View this thread: http://www.excelforum.com/showthread...hreadid=478463 |
Help with Time Sheet
Suppose that the result is in D2. Try the formula:
=INT(D2*1440/15+0.4999)/1440*15 -- JRod Microsoft MVP - Excel http://EXCELer.blogspot.com "CRobey4393" escreveu na mensagem ... Hi, My name is Chuck and I am trying to write a spreadsheet to be used as a daily timesheet. The way it is set is: c6 is Time In, d6 Time out for Lunch, e6 Time in from lunch and f6 is Time Out. The formula I am using for total time is (d6-c6)+(f6-e6) I need to round this to the 1/4 hour. Any help is appreciated. Chuck -- CRobey4393 ------------------------------------------------------------------------ CRobey4393's Profile: http://www.excelforum.com/member.php...o&userid=28257 View this thread: http://www.excelforum.com/showthread...hreadid=478463 |
Help with Time Sheet
Another approach, brought By Norman Harker sometimes ago:
=ROUND((D2)/"00:15:00";0)*"00:15:00" P.S.- don't forget, in both cases, to format the cell as Time hh:mm Regards -- JRod Microsoft MVP - Excel http://EXCELer.blogspot.com "JRod" escreveu na mensagem ... Suppose that the result is in D2. Try the formula: =INT(D2*1440/15+0.4999)/1440*15 -- JRod Microsoft MVP - Excel http://EXCELer.blogspot.com "CRobey4393" escreveu na mensagem ... Hi, My name is Chuck and I am trying to write a spreadsheet to be used as a daily timesheet. The way it is set is: c6 is Time In, d6 Time out for Lunch, e6 Time in from lunch and f6 is Time Out. The formula I am using for total time is (d6-c6)+(f6-e6) I need to round this to the 1/4 hour. Any help is appreciated. Chuck -- CRobey4393 ------------------------------------------------------------------------ CRobey4393's Profile: http://www.excelforum.com/member.php...o&userid=28257 View this thread: http://www.excelforum.com/showthread...hreadid=478463 |
Help with Time Sheet
Sandy,
This is really another approach, ok!! But all of the solutions they seem that have the same problem: the rounding is made by deffect (I don't know if this is the right word...perhaps "down"?) I found another approach presented by Jon Peltier, that seems work better, because if it is, for instance 17:02, goes to 17:15 and not to 17:00... The formula is: =CEILING(D2,D3) D2 is the result and D3 has the content: 0:15... In that case, the formula gets a rounded up value. Regards. -- JRod Microsoft MVP - Excel http://EXCELer.blogspot.com "Sandy Mann" escreveu na mensagem ... "JRod" wrote in message ... =INT(D2*1440/15+0.4999)/1440*15 Seems like an awful lot of calculating, why not just: =ROUND(D2*96,0)/96 -- Regards Sandy Replace@mailinator with @tiscali.co.uk "JRod" wrote in message ... Suppose that the result is in D2. Try the formula: =INT(D2*1440/15+0.4999)/1440*15 -- JRod Microsoft MVP - Excel http://EXCELer.blogspot.com "CRobey4393" escreveu na mensagem ... Hi, My name is Chuck and I am trying to write a spreadsheet to be used as a daily timesheet. The way it is set is: c6 is Time In, d6 Time out for Lunch, e6 Time in from lunch and f6 is Time Out. The formula I am using for total time is (d6-c6)+(f6-e6) I need to round this to the 1/4 hour. Any help is appreciated. Chuck -- CRobey4393 ------------------------------------------------------------------------ CRobey4393's Profile: http://www.excelforum.com/member.php...o&userid=28257 View this thread: http://www.excelforum.com/showthread...hreadid=478463 |
| All times are GMT +1. The time now is 12:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com