Show missing data as 0

M

Mike

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?
 
J

JulieD

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
 
P

Peo Sjoblom

Maybe something like

=IF(ISNUMBER(FIND("d",E3)),"your
formula",24*(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E3,"h",":"),"m","")," ","")))
 
M

Mike

It works great... Thanks Julie!!

JulieD said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If statement containing today formula 8
Date format 2
Add Times 3
Help with creating a Macro 6
Can I get Excel to call my macro for number formatting? 5
Critique a function to convert time, please 2
Custom Formatting: Can I set up a condition? 0
Excel VBS time difference functions<[email protected] 0

Top