Convert day, minutes hours into hours:minutes

[Donna Grant]

I have a spreadsheet with all cells in general format and for example
the data follows along the lines of 3 days, 22 hours, 48 minutes. I
would like to change the format so that it goes to 94:48. Is there any
way of running something to make it change to this? I am using Excel
2003 but I'm sure it would be simular in most versions.

 

[David McRitchie]

Hi Donna,
Format as [h]:mm so that hours do not overflow into days.
=

 

[Peo Sjoblom]

Is it text or are the values formatted dates? If the latter just use custom
format

[hh]:mm

if the former post back with more info whether there are in different cells
or
how 1 day 1 hour 1 minute would look?

 

[Norman Harker]

Hi Peo & Donna!

In the text form, the following seems to work OK for various options:

=LEFT(A1,FIND("
",A1)-1)+MID(A1,FIND("h",A1)-3,2)/24+MID(A1,FIND("m",A1)-3,2)/(24*60)
Format [hh]:mm

3 days, 22 hours, 48 minutes
Returns: 94:48

3 days, 2 hours, 7 minutes
Returns: 74:07

1 day, 1 hour, 1 minute
Returns: 25:01

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Donna Grant]

The cells are just formatted to general. Can you explain how to enter
the following solution, I can't get it to work. I enter it into a
blank cell and it doesn't work at all just stays as text.

 

[Norman Harker]

Hi Donna!

Select the formula below:

=LEFT(A1,FIND("",A1)-1)+MID(A1,FIND("h",A1)-3,2)/24+MID(A1,FIND("m",A1
)-3,2)/(24*60)

Right Click > Copy
Select the cell you want it in.
Right click the formula bar
Paste

You may get a blank line at the end. Just back space. Similarly if
there's a break in the formula bar into two lines because of word wrap
in the newsreader.

You'll need to replace the A1 by the cell containing your time data.

If you still have problems, I'll send you a workbook with the formula
in and you can copy and paste from the formula bar of that workbook.
Or send me yours.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Donna Grant]

Can you please send me the workbook because I still haven't got it working!

It just displays #Value!