Back

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]:mm

You 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)/96

There 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.