Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to convert number of days to months | Excel Discussion (Misc queries) | |||
How to convert a number to years, months, days? | Excel Worksheet Functions | |||
how do i convert a number of days to years, months & days? | Excel Discussion (Misc queries) | |||
how do I convert a number to number of years, months and days | Excel Worksheet Functions | |||
convert dates to number of days | Excel Worksheet Functions |