| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 39463 | 2003-11-07 04:39:00 | time formulas in Excel | Alison (2911) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 189908 | 2003-11-07 04:39:00 | I want to set up a table for a friend relating lap times to length of a race but am having trouble converting the total time calculated in seconds into a mm:ss format. e.g lap time for 400 m is 56 s, so for an 800 m race, total time is 56 x (800/400) = 112 s. The formula is easy but I can't convert the 112 seconds to 1 min 52 secs. I tried Format Cell then Custom then mm:ss option after entering the formula, but it just gives me 00.00. Is there an easy solution to this please someone? | Alison (2911) | ||
| 189909 | 2003-11-07 05:39:00 | Where the 112 is in cell A2, put this in B2: =INT(A2/60)&":"&(A2-INT(A2/60)*60) It rounds the seconds divided by 60 to the nearest whole number below, then takes that number and subtracts it from the total, add the colon in the middle with &":"& and turn the thing into a text string. Good luck. robo. |
robo (205) | ||
| 189910 | 2003-11-07 20:03:00 | Another way is to divide the seconds in cell a1 by 1440 (in cell b2 would be formula =a1/1440), and format b2 to mm:ss. | Craigb (688) | ||
| 189911 | 2003-11-08 08:39:00 | Thanks for the suggestions guys. Tried robo's one which worked but would only display 2:20 mins as 2.2 so tried Craigb's which worked fine once I changed the conversion factor to 1440 x 60 to give mm:ss as otherwise it was hh:mm. Good refresher experience for me as I haven't done anything in Excel for over 5 years. | Alison (2911) | ||
| 1 | |||||