Thread: Another Excel question

I need time right now its in this format 1.05 (1:05) to be added to 4AM. Here is a link to the spreadsheet.

you should be able to, format cell -> time, and that will convert decimal hours to minutes

When I try it takes for example 1.31 and converts it to 7:26:24?

on the linked spreadsheet the '123' button might do the trick.

It might require some editing as .3 of an hour would convert to :20 not :30

I forgot how much I hate the time format in excel, looks like the problem is in the changing the format through the formula.

From what I can gather you are multiplying C3 (a mileage unit) by a fraction (13/60) to get a time. Changing the fraction to a hours:minutes format might help. It might also be easiest to create another column with the time value (13/60) in it; formatted for time, to be entered into your formula for column H. That might, with the format of H set to a time format, work.

No guarantees.

Yeah this is a spreadsheet for my crew this weekend so that they can figure out what time I should be arriving at the various aid stations. Its based on a 13 min mile. Thats why I /60 I wanted 12.5 min mile but that seemed to complicate things.

yeah the first thing you gotta do is get your time in the right format. 1 hr 5 minutes is 1.083 hours. Convert your existing format to decimal hours like this:
=VALUE(LEFT(A4,FIND(".",A4)-1))+(VALUE(MID(A4,FIND(".",A4),LEN(A4)-FIND(".",A4)+1))*100/60) (change a4 to whatever cell is holding your 1.05 time format)

I would work in decimal hours and then convert it back to hr/min for convenience at the end:
=LEFT(A9,FIND(".",A9)-1)&":"&(VALUE(MID(A9,FIND(".",A9)+1,LEN(A9)-FIND(".",A9)))/100*60) (change a9 to whatever cell is holding your final decimal hours values)

probably an easier way to do this but I work with goofy spreadsheets all the time and string functions usually get er dun

i'd try it in minutes, then converting, just a guess.
And i'd slow it down to 13min miles to make the math easier, and so i didn't hurl quite so early in the race.

good luck!

Thanks again mtnwriter thats twice you have worked some Excel magic.