Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Show missing data as 0
My data has days hours minutes in cell E2. 3d 04h 58m
I use the following to convert it to hours: =LEFT(E2,FIND("d",E2)-1)*24+MID(E2,FIND("h",E2)-2,2)+MID(E2,FIND("m",E2)-2,2)/60 It work fine until I get a unit without the day. 04h 58m. Can I use a if statement to add all numbers even if the day value is missing? |
#2
|
|||
|
|||
Hi Mike
try =IF(iserror(FIND("d",E2)),0,LEFT(E2,FIND("d",E2)-1)*24)+MID(E2,FIND("h",E2)-2,2)+MID(E2,FIND("m",E2)-2,2)/60 Cheers JulieD "Mike" wrote in message ... My data has days hours minutes in cell E2. 3d 04h 58m I use the following to convert it to hours: =LEFT(E2,FIND("d",E2)-1)*24+MID(E2,FIND("h",E2)-2,2)+MID(E2,FIND("m",E2)-2,2)/60 It work fine until I get a unit without the day. 04h 58m. Can I use a if statement to add all numbers even if the day value is missing? |
#3
|
|||
|
|||
Maybe something like
=IF(ISNUMBER(FIND("d",E3)),"your formula",24*(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E3," h",":"),"m","")," ",""))) -- Regards, Peo Sjoblom "Mike" wrote in message ... My data has days hours minutes in cell E2. 3d 04h 58m I use the following to convert it to hours: =LEFT(E2,FIND("d",E2)-1)*24+MID(E2,FIND("h",E2)-2,2)+MID(E2,FIND("m",E2)-2,2 )/60 It work fine until I get a unit without the day. 04h 58m. Can I use a if statement to add all numbers even if the day value is missing? |
#4
|
|||
|
|||
It works great... Thanks Julie!!
"JulieD" wrote: Hi Mike try =IF(iserror(FIND("d",E2)),0,LEFT(E2,FIND("d",E2)-1)*24)+MID(E2,FIND("h",E2)-2,2)+MID(E2,FIND("m",E2)-2,2)/60 Cheers JulieD "Mike" wrote in message ... My data has days hours minutes in cell E2. 3d 04h 58m I use the following to convert it to hours: =LEFT(E2,FIND("d",E2)-1)*24+MID(E2,FIND("h",E2)-2,2)+MID(E2,FIND("m",E2)-2,2)/60 It work fine until I get a unit without the day. 04h 58m. Can I use a if statement to add all numbers even if the day value is missing? |
#5
|
|||
|
|||
you're welcome and thanks for the feedback
"Mike" wrote in message ... It works great... Thanks Julie!! "JulieD" wrote: Hi Mike try =IF(iserror(FIND("d",E2)),0,LEFT(E2,FIND("d",E2)-1)*24)+MID(E2,FIND("h",E2)-2,2)+MID(E2,FIND("m",E2)-2,2)/60 Cheers JulieD "Mike" wrote in message ... My data has days hours minutes in cell E2. 3d 04h 58m I use the following to convert it to hours: =LEFT(E2,FIND("d",E2)-1)*24+MID(E2,FIND("h",E2)-2,2)+MID(E2,FIND("m",E2)-2,2)/60 It work fine until I get a unit without the day. 04h 58m. Can I use a if statement to add all numbers even if the day value is missing? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
missing data from table | Excel Discussion (Misc queries) | |||
missing data from table | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |