Home |
Search |
Today's Posts |
#1
|
|||
|
|||
re calculating numbers to times
Hello I hope somebody can help me with the following: Has anybody ever tried to recalculate numbers to times? Let me be more specific: if I have 2 times, let's say 37:28 seconds and 36:94 seconds, how can I re-calculated this to 1:14:22? Adding the 28 and 94 gives me 1 sec 22: for the seconds: (int(122/100)=1, the rest = 122 -/- (int(122/100)*100) =22 37 and 36 = 73 which equals 1 min 13 secs (similar: mins = int(73/60), secs = 73 -/- (int(73/60)*60) = 13 Together: 1 min, 13 + 1 = 14 seconds and 22 My problem is that I don't know how to translate this into a formula, or better VBA? I think that if I want to capture this only in formulas I may need a lot of columns for 1 calculation, so if it is possible in VBA this has my preference! Who can help me? Thanks for your help Hein -- Hein ------------------------------------------------------------------------ Hein's Profile: http://www.excelforum.com/member.php...o&userid=24744 View this thread: http://www.excelforum.com/showthread...hreadid=472455 |
#2
|
|||
|
|||
I assume you mean 37 and 28/100 seconds, and 36 and 94/100 seconds? If so you
can enter these times in a worksheet cell using this data entry format: 00:00:37.28 and 00:00:36.94. Set the format of the cells to hh:mm:ss.00 Then simply add the cells (e.g. =A1+B1). YOu may have to set the format of the result cell, too. You can also enter the numbers as 37.28 and 36.94. Then to add them, use the formula =(A1+B1)/86400 and format as above. Excel stores a time as a fraction of a day. The division by 86400 converts seconds to days. On Sun, 2 Oct 2005 14:49:22 -0500, Hein wrote: Hello I hope somebody can help me with the following: Has anybody ever tried to recalculate numbers to times? Let me be more specific: if I have 2 times, let's say 37:28 seconds and 36:94 seconds, how can I re-calculated this to 1:14:22? Adding the 28 and 94 gives me 1 sec 22: for the seconds: (int(122/100)=1, the rest = 122 -/- (int(122/100)*100) =22 37 and 36 = 73 which equals 1 min 13 secs (similar: mins = int(73/60), secs = 73 -/- (int(73/60)*60) = 13 Together: 1 min, 13 + 1 = 14 seconds and 22 My problem is that I don't know how to translate this into a formula, or better VBA? I think that if I want to capture this only in formulas I may need a lot of columns for 1 calculation, so if it is possible in VBA this has my preference! Who can help me? Thanks for your help Hein |
#3
|
|||
|
|||
Thank you Myrna for your reply, I think I was a bit hasty posting my question, I should have been clearer. The situation is as follows: The mentioned times do not have the ":". In fact the 37:28 and 36:94 are written as 3728 and 3694 ànd are text values. Maybe I should try to make my own formula in VBA? Maybe I should explain what it is I want to do: I am building a database for speedskating times. This database is fed with data I first prepare in Excel. One type of data are laptimes. Sometimes I only have the times for each lap, sometimes I only have the accumulated times, adding up to the final time. My goal is to calculate the missing times, starting with adding up the laptimes to a final time. The first step in this is trying to add the 3728 and 3694. How can I create a formula in VBA that adds up these numbers, taking into account: 28 + 94 = 122. This is 1 second, 22/100 37 + 36 = 73. This is 1 min, 13 secs Together: 1 mins, 13 + 1 sec, and 22/100: 1:14:22 Is there a way to do this Hope you can help me Thanks Hein -- Hein ------------------------------------------------------------------------ Hein's Profile: http://www.excelforum.com/member.php...o&userid=24744 View this thread: http://www.excelforum.com/showthread...hreadid=472455 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating growth with negative numbers | Excel Worksheet Functions | |||
NEED HELP - Calculating dates and times | Excel Discussion (Misc queries) | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions | |||
GET NON-ZERO RESULTS USING STDEV FOR CERTAIN NUMBERS | Excel Worksheet Functions | |||
Formulas stop calculating at random times when editing a few spreadsheets. | Excel Discussion (Misc queries) |