how do i convert a text of 15h05m to number of days
 

Hi,

I'm pulling in some data to show elapsed time and it's pulling it in as
Hours & Minutes (example - 15h05m as text), but I'd like it to show me in
days.

Can anyone help

how do i convert a text of 15h05m to number of days
 

Do you mean that you want to show 15h05m as days (0.628472) ?

If you have 15h05m in A1, you can use ths formula:

=LEFT(A1,SEARCH("h",A1)-1)/24+RIGHT(LEFT(A1,LEN(A1)-1),2)/24/60

Hope this helps.

Pete

On Nov 28, 11:37*am, David wrote:
Hi,

I'm pulling in some data to show elapsed time and it's pulling it in as
Hours & Minutes (example - 15h05m as text), but I'd like it to show me in
days.

Can anyone help

how do i convert a text of 15h05m to number of days
 

If you mean to show
15h05m like 0 days 15:05
25h05m like 1 days 01:05
etc. then
=INT(LEFT(D12,2)/24)&" days
"&TEXT(MOD(TIME(LEFT(D12,2),MID(D12,4,2),0),1) ,"[h]:mm")

(15h05m text being in D12)

Regards,
Stefi


€žDavid€� ezt Ã*rta:

Hi,

I'm pulling in some data to show elapsed time and it's pulling it in as
Hours & Minutes (example - 15h05m as text), but I'd like it to show me in
days.

Can anyone help

how do i convert a text of 15h05m to number of days
 

Another formula to use in a helper cell:
=--SUBSTITUTE(UPPER(SUBSTITUTE(UPPER(A1),"H",":")),"M ",":00")

and use a custom format of:
d \D\a\y\s hh:mm


David wrote:

Hi,

I'm pulling in some data to show elapsed time and it's pulling it in as
Hours & Minutes (example - 15h05m as text), but I'd like it to show me in
days.

Can anyone help

--

Dave Peterson

how do i convert a text of 15h05m to number of days
 

=--SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"m","")

Custom format: d "days" h:mm


"David" wrote:

Hi,

I'm pulling in some data to show elapsed time and it's pulling it in as
Hours & Minutes (example - 15h05m as text), but I'd like it to show me in
days.

Can anyone help

how do i convert a text of 15h05m to number of days
 

That format is OK until you get beyond 31 days
--
David Biddulph

"Teethless mama" wrote in message
...
=--SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"m","")

Custom format: d "days" h:mm


"David" wrote:

Hi,

I'm pulling in some data to show elapsed time and it's pulling it in as
Hours & Minutes (example - 15h05m as text), but I'd like it to show me in
days.

Can anyone help