Hiding #VALUE! from view

[simplelife]

Hello eveyone,

I am finished with my simple excel file. The formula in F4 is working fine(thanks to a user on this page!), however it is displaying #VALUE!. Once I input date and ZULU time into D4 and E4 I see the right calculation. I just want to hide #VALUE! from view and dont need to change anything. I have all ready turned off error checking via options, formulas, uncheck enable error checking. What else can I do??

Thanks a lot for any help!


-Jonathan

HOBBS MASTER.xlsx

[FDibbins]

Hi and welcome to the forum

Try this adjustment...
=IF(OR(D3="",E3=""),"",TEXT((DATEVALUE("20"&RIGHT(LEFT(E3,FIND(" ",E3)-1),2)&"/"&LEFT(E3,FIND("/",E3,FIND("/",E3)+1)-1))+TIMEVALUE(LEFT(RIGHT(E3,5),2)&":"&LEFT(RIGHT(E3,3),2))-(DATEVALUE("20"&RIGHT(LEFT(D3,FIND(" ",D3)-1),2)&"/"&LEFT(D3,FIND("/",D3,FIND("/",D3)+1)-1))+TIMEVALUE(LEFT(RIGHT(D3,5),2)&":"&LEFT(RIGHT(D3,3),2)))),"[h]+mm"))

[djapigo]

If you are using Excel 2007+, there is a new function called IFERROR...

But it looks like you are still using 2003, so you can use this... which I'm assuming E4 has to be filled in for your formula to work...

=IF(E3="","",your formula)

[simplelife]

Thanks a lot FDibbins. It worked perfectly!

[FDibbins]

Your profile says 2003, but the file you uploaded suggests 2007 or later? Please update your profile

If you are in fact using 2007 or later, djapigo's IFERROR() suggestion would be a better option to try

[Teethless mama]

75% shorter / elegant

=IFERROR(TEXT(SUBSTITUTE(REPLACE(E3,LEN(E3)-2,,":"),"Z",)-SUBSTITUTE(REPLACE(D3,LEN(D3)-2,,":"),"Z",),"[h]+mm"),"")

[FDibbins]

Teethless, its all very well giving "75% shorter / elegant" formulas if the OP understands what you are doing. Without a detailed explaination, your fancy formulas are pretty much usless to anyone else who wants to replicate or modify them.

So perhaps consider including a "how it works", or why even bother?

[Teethless mama]

Teethless, its all very well giving "75% shorter / elegant" formulas if the OP understands what you are doing. Without a detailed explaination, your fancy formulas are pretty much usless to anyone else who wants to replicate or modify them.

So perhaps consider including a "how it works", or why even bother?

You're very rude and should be banned this forum

[FDibbins]

How is asking for an explanaition of your formula rude?

and also...
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

[Tony Valko]

If you enter the dates/times as true Excel dates/times then this would be very simple.

Instead of this:

6/4/08 1406Z..........6/5/08 0402Z

Do this:

6/4/08 2:06 PM......6/5/08 4:02 AM

Then, you can use this much simpler and efficient formula:

=IF(COUNT(D3:E3)<2,"",E3-D3)

Format as [h]:mm

Why fight with Excel when you can work with it?

[FDibbins]

Thanks Tony, very slick and eligant

[Tony Valko]

So, what % shorter and elegant is my suggestion?