About MrExcel Consulting Services Learn Excel Resources Challenge of the Month MrExcel Seminars Message Board MrExcel Store Podcast Search Contact Home

 Past Tip of the Day Delaine asks: I need to enter a series of time values in military time. Then, I need to total them up and round to the nearest quarter hour.To enter values in military time, select those cells and then Format > Cells. Click the Number tab. Click Time in the left listbox. Select 13:30 as the number format. You can then enter the times.To get a total in cell A99 of all the times, you first must format cell A99 to show hours in excess of 24. Select A99, Format > Cells. Click Custom in the left listbox. In the Custom Format box type: [h]:mmYou can now enter a formula to sum the times, something like =SUM(A2:A98). Since you want to round to the nearest quarter hour, use this formula instead:=ROUND(SUM(A2:A99)*96,0)/96There are 96 quarter-hours in a day. Multiplying the total time by 96, rounding, and then dividing by 96 will give you the nearest quarter hour. By Bill Jelen on 29-Oct-2001 MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.