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

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

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)

"chris" <[email protected]> wrote in message
news:[email protected]...
> 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

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)

"chris" <[email protected]> wrote in message
news:[email protected]...
> 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

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

"chris" <[email protected]> wrote in message
news:[email protected]...
>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

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.



chris wrote:
>
> 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

--

Dave Peterson

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.

--
Regards,
Fred


"chris" <[email protected]> wrote in message
news:[email protected]...
>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