Excel

[Donna Grant]

I have asked this question before but the solution given by an MVP
didn't work and after telling them so no response was given so I'll
try again.....

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.

The solution I was given was:

=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

but it just displays #Value!

Thanks!

 

[Frank Kabel]

Hi Donna
the formula works fine besides that there is a 'd' missing in the first
FIND function. Try
=LEFT(A1,FIND("d",A1)-1)+MID(A1,FIND("h",A1)-3,2)/24+MID(A1,FIND("m",A1
)-3,2)/(24*60)

HTH
Frank

 

[Bob Phillips]

Donna,

I also had a problem with this formula, but that was because the first FIND
was looking for nothing rather than space, and I am not sure whether this
was a transcription problem. When I corrected this it worked okay.

In the formula, make sure that it reads
FIND(" ",A1)
not the space between the quotes

and it should work.

Also, make sure the cell is formatted as [h]:mm to cater for more than 24
hours.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul]

Donna,
A lateral thought just in case, as your original post could be read in
different ways. Just ignore if I'm completely wrong!

The formula you have been given assumes that you have text such as "3 days,
22 hours, 48 minutes" all within one cell (A1). Maybe you have entries for
days, hours and minutes in three separate cells, such as A1, B1 and C1?

Donna,

I also had a problem with this formula, but that was because the first FIND
was looking for nothing rather than space, and I am not sure whether this
was a transcription problem. When I corrected this it worked okay.

In the formula, make sure that it reads
FIND(" ",A1)
not the space between the quotes

and it should work.

Also, make sure the cell is formatted as [h]:mm to cater for more than 24
hours.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have asked this question before but the solution given by an MVP
didn't work and after telling them so no response was given so I'll
try again.....

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.

The solution I was given was:

=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

but it just displays #Value!

Thanks!

 

[John McGimpsey]

The formula works fine for me. I suspect you're a victim of line break.
Try:

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

where the formula is all one continuous entry with no returns or
linefeeds.

You can download a sample workbook here:

ftp://ftp.mcgimpsey.com/excel/donnagrant_demo.xls

 

[Arvi Laanemets]

Hi

A straightforward solution - cumbersome but relatively foolproof (as long as
there are no typos):
=LEFT(A1;(FIND("
day";A1;1)-1))*1+LEFT(RIGHT(A1;LEN(A1)-FIND(",";A1)-1);(FIND("
hour";RIGHT(A1;LEN(A1)-FIND(",";A1)-1);1)-1))/24+LEFT(RIGHT(RIGHT(A1;LEN(A1)
-FIND(",";A1)-1);LEN(RIGHT(A1;LEN(A1)-FIND(",";A1)-1))-FIND(",";RIGHT(A1;LEN
(A1)-FIND(",";A1)-1))-1);(FIND("
minute";RIGHT(RIGHT(A1;LEN(A1)-FIND(",";A1)-1);LEN(RIGHT(A1;LEN(A1)-FIND(","
;A1)-1))-FIND(",";RIGHT(A1;LEN(A1)-FIND(",";A1)-1))-1);1)-1))/(24*60)

and format as [h]:mm
(you have to replace all semicolons with commas in formula probably - there
was too much of them to edit for me without testing)

 

[Arvi Laanemets]

Hi

His problems are with timestrings which have '1 day, ' or '1 hour,' in it.


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets

The formula works fine for me. I suspect you're a victim of line break.
Try:

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

where the formula is all one continuous entry with no returns or
linefeeds.

You can download a sample workbook here:

ftp://ftp.mcgimpsey.com/excel/donnagrant_demo.xls

I have asked this question before but the solution given by an MVP
didn't work and after telling them so no response was given so I'll
try again.....

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.

The solution I was given was:

=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

but it just displays #Value!

Thanks!

 

[John McGimpsey]

Still works fine for me as long as there is at least one space between a
comma and a number and one space between the number and the "h" or the
"m".

1 day, 1 hour, 1 minute ====> 25:01

 

[Donna Grant]

Thanks John! My problem is now fixed. Thank you to everyone else who helped out.