can we convert "2 days 16 hrs" to " 64hrs" using excel functions

C

chris

i tried diffeernt methods. i can either convert days to hrs or hrs to days.
but am not able to do both. so if anybody can do it. plz let me know.

thanks
chris
 
B

Bob Phillips

If the data is how you stated, this will work

=LEFT(A1,FIND(" ",A1)-1)*24+MID(SUBSTITUTE(A1," hrs",""),FIND(" ",A1,FIND("
",A1)+1)+1,99)&"hrs"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

slightly better

=LEFT(A1,FIND(" ",A1)-1)*24+MID(TRIM(SUBSTITUTE(A1,"hrs","")),FIND("
",A1,FIND(" ",A1)+1)+1,99)&"hrs"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Biff

Hi!

Maybe something like this:

=IF(ISNUMBER(SEARCH(" day",A1)),24*LEFT(A1,FIND("
",A1)-1),0)+IF(ISNUMBER(SEARCH(" hr",A1)),TRIM(RIGHT(SUBSTITUTE(A1,"
hrs",""),2)),0)

It'll work on the following formats:

2 days
99 hrs
0 days 0 hrs
2 days 99 hrs
(any number of days) days ( up to any 2 digits) hrs

Biff
 
D

Dave Peterson

I'm not sure if this is a possibility for you, but if you used two cells -- one
for hours and one for days, then your life may get easier.
 
F

Fred Smith

If you have an actual Excel time in your cell, the format [h] will display the
64 hours for you. The brackets tell excel to display more than 24 hours.
 
Top