# Thread: Working with hours and minutes

1. ## Working with hours and minutes

I have a project that I have to work with hours:minutes.

Is there an easy way of entering the data without me putting in the colon : symbol.

Could I format the cells to accept the Hours and Minutes directly

The next item is to add and subtract the data entered. I formated the cells with HR:MIN and then tried addition and then subtraction. That seem to work ok but the actual data is a time and not really Hrs and Minutes.

Could Excel do the two items that I am trying to do? if so How do you do them.

Thanks

I am including a worksheet for illustrative purpose.

2. For the last question, if you mean you want the hours to add up over 24 then using your example, in cell B4 format that using Custom (I think its called that in English) and use [hh]:mm. For example if you have three days of 20 hrs and 25 mins it will display 61:15 the actual total of hours rather than convert it to days, hours and minutes.
An easier way to add the cells together is to click on the cell where you want your total, in this case B4 then click the AutoSum button under the Formula tab. Its easier than typing out B1+B2+B3 etc.

3. Alan answered the formatting issue. Just to clarify:
you had it formatted as h:mm which is a time of day so can only display a number <24 (23:59:59.99999 is the max). The [h]:mm gives the elapsed time and can be >24. Likewise [m]:ss will give elapsed min with secs and [s] will give elapsed seconds if those are desired.

To enter without the colon you can use a macro to convert. See Chip Pearson's website at http://www.cpearson.com/excel/datetimeentry.htm for some code and explanation. If you do not want the worksheet change code (which disables the UNDO) a formula alternative is just to enter the hrs and mins in different cells and then use the time function to convert [=TIME(h,m,s)] before doing the arithmetic.

Steve

4. I'm not sure if I explained myself. I need only hours and minutes and to be able to key them in without inserting a colon each time by letting the format do that. is that a possibility?

5. Steve answered that in the second part of his answer.

cheers, Paul

6. The format does not change the number only the display of the number. You can format as:
00":"00
to get all 4 digits and add the colon. But you shouldn't do math directly with these numbers as the are still 4 digit numbers and aren't time. [for example 1130 + 35 = 1165 not 1205 as you would want]

You would have to calculate a timevalue for each of them them with something like:
=TIME(LEFT(RIGHT("0000"&A1,4),2),RIGHT(A1,2),0)
and then do the math on the converted numbers. The macros on Pearson's page do the conversion, making them time after the values are entered

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•