Results 1 to 10 of 10
Thread: adding time (97)

20020701, 14:17 #1
 Join Date
 Mar 2001
 Posts
 85
 Thanks
 0
 Thanked 0 Times in 0 Posts
adding time (97)
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...

20020701, 14:23 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: adding time (97)
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.
Legare Coleman

20020701, 14:29 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: adding time (97)
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.
John ... I float in liquid gardens
UTC 7ąDS

20020701, 14:45 #4
 Join Date
 Mar 2001
 Posts
 85
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: adding time (97)
thanks guys,
it's a bast*rd to the data entry though  numpad with right hand, shift colon with left hand...

20020701, 16:10 #5
 Join Date
 Mar 2001
 Posts
 85
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: adding time (97)
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

20020701, 16:29 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: adding time (97)
When you select one of these, exactly what shows in the formlua bar, and what is the cell format?
John ... I float in liquid gardens
UTC 7ąDS

20020701, 17:34 #7
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: adding time (97)
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 SubJohn ... I float in liquid gardens
UTC 7ąDS

20020701, 21:42 #8
 Join Date
 Jun 2002
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: adding time (97)
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.

20020701, 22:55 #9
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,174
 Thanks
 2
 Thanked 454 Times in 373 Posts
Re: adding time (97)
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),(A1INT(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 ...Cheers,
Paul Edstein
[MS MVP  Word]

20020702, 02:20 #10
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 201
 Thanks
 8
 Thanked 0 Times in 0 Posts
Re: adding time (97)
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