If conditions having #VALUE

[Harold Garcia]

¦ 8:34 ¦ <colgroup><col width="89"></colgroup><tbody> </tbody>	8:34 AM <colgroup><col width="103"></colgroup><tbody> </tbody>
¦18:46 ¦	6:46 PM <colgroup><col width="103"></colgroup><tbody> </tbody>
¦ ¦	#VALUE! <colgroup><col width="103"></colgroup><tbody> </tbody>
____________	#VALUE! <colgroup><col width="103"></colgroup><tbody> </tbody>
Day-Off ¦ <colgroup><col width="103"></colgroup><tbody> </tbody>	#VALUE! <colgroup><col width="103"></colgroup><tbody> </tbody>

<tbody>
</tbody>

this is the original formula
=TIMEVALUE(SUBSTITUTE(A1,"¦",""))

Problem is when i drag the data it put #VALUE...
I need to return the Day-Off ¦ as D-Off
and the no data as blank...

 

[NBVC]

You can use IFERROR()

e.g

=IFERROR(TIMEVALUE(SUBSTITUTE(A1,"¦","")),"")

 

[Harold Garcia]

How bout the data having Day-Off ¦?

I want to look it this way D-Off...

 

[NBVC]

Try:

=IFERROR(IF(ISNUMBER(SEARCH("Day-off",A1)),"D-off",TIMEVALUE(SUBSTITUTE(A1,"¦",""))),"")

 

[Harold Garcia]

Can i add several condition?

Like FL, SL etc...

 

[NBVC]

You can create a little table somewhere with the search criteria (e.g Day-off) and the corresponding returns (e.g D-off) ...

then try:

=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(A1,"¦","")),$X$1:$Y$10,2,0),IFERROR(TIMEVALUE(SUBSTITUTE(A1,"¦","")),""))

where X1:Y10 contains your lookup table.

 

[Harold Garcia]

Thanks very much...

This will help a lot...

Actually this is just part of the solution of my bigger problem...

Can you help me in this link
http://www.mrexcel.com/forum/excel-questions/748777-all-excel-experts.html

God Bless

 

[NBVC]

I have contributed to your thread with a basic start based on the above.

 

[Harold Garcia]

Thanks so much...

God Bless