I simply need to add time.

I'd like to be able to enter (e.g) "11.30" in A1 (i.e 11 hrs 30min) , "13.30" (ie 13 hrs 30 min) in A2 and in A3 get the result 25 (i.e. 25hrs) when I add them together.

Seems impossible...

You need to use a custom format for the cell that contains the sum. The custom format should be [h]:mm (with the brackets around the h) if you want elapsed hours and minutes. If you also want seconds then use [h]:mm:hh. You should also probably be using a colon between the hour and the minute when you enter the time unless your separator has been changed to use the decimal point, otherwise, 11.30 is not 11 hours and 30 minutes, it is 11 days and a little less than 8 hours.

Adding a note to Legares' post: if you are going to calculate time differences across days, such as from 11:30 pm to 1:30 am next day, you will need to track the dates, or do some presumptive logic by adding 12 hours if start time is less than end time.

thanks guys,

it's a bast*rd to the data entry though - numpad with right hand, shift colon with left hand...

just realised I've got another problem.

I've been given a ss to work on, with all the "times" already entered. Trouble is, I've got a feeling XL thinks it's text. For eg in A1 there's 1:30, in A2 1:45. However when I add them up in A3 I get 0, rather than 3.15. So I guess the originator of the ss tried(?) to enter the numbers in time format (hence the colon), but for some reason things have gone pear shaped.

We're talking thousands of numbers here, so I hope there's some way of converting what I've got into calculable times.

Many TIA

When you select one of these, exactly what shows in the formlua bar, and what is the cell format?

Pending your answer to my other question, and assuming the data is in columns, see if this works. You need to select any cells in each column or adjacent columns containing the text which should be time values:

Sub Text2Time()
Dim rngCell As Range
Dim rngTemp As Range
On Error Resume Next
Set rngTemp = Selection.EntireColumn.SpecialCells(xlCellTypeCons tants, 2)
If Err.Number = 1004 Then
MsgBox "No text cells in Selected Column"
Exit Sub
End If
rngTemp.NumberFormat = "h:mm"
For Each rngCell In rngTemp
rngCell.Value = rngCell.Value
Next rngCell
End Sub

One solution may be to "force" the cells to be numeric. I've recovered text (values, dates and times) this way many times: type a 1 in any cell; copy that cell; select the range to be fixed; from a right click or the full menu, choose Paste Special, then choose values and MULTIPLY. It's the multiplying by 1 (which doesn't change the value) that forces it to be a value and not text.

If the times have already been entered in hh.mm format, either as text or numbers, the following formula will convert them to time values in hh:mm format:
=TIME(INT(A1),(A1-INT(A1))*100,)
You'll need to be sure that what you've got is really in in hh.mm format and not a decimal hors format (eg 1.75 for 1:45).

If the times have been entered as text in hh:mm format, , the following formula will convert them to time values in hh:mm format:
=TIME(LEFT(A1,FIND(":",A1)-1),MID(A1,FIND(":",A1)+1,LEN(A1)-FIND(":",A1)),)

Both could be coded as macros to replace the errant entries, but I don't have the time right now ...

Hi CJ,

Checkout Chip Pearson's great site for details of quick entry of dates and times. I modified it slightly to use in Oz (Australia!) and to let those who don't know enter times and dates the slow way as well.

Good Luck!

Peter Moran

Posting Permissions

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