Excel Time Problem

I am using excel to try and calculate drive time between 2 different time zones. So in Cell B26 I have 6:56 which is formatted h:mm. In cell C26 I have "CST", in Cell D26 I have the formula:  IF(OR(B26="",B26=F26),"",F26+(MATCH(UPPER(E26),{"EST","CST","MST","PST"},0)-1)/24-B26-(MATCH(UPPER(C26),{"EST","CST","MST","PST"},0)-1)/24). In Cell E26 I have "EST", and in cell F26 I have 7:09 in the same h:mm format.

Now in cell D26, where I have the long formula above it is formatted to [h]:mm. The problem is with the example listed above, I am getting the "########" error in D26. I believe this is because the 2 times listed are less than an hour. Any assistance would be most helpful.
Work-Hour-Tracker--V2-.xlsx
Humb13St3psAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
"########"  usually means that the column isn't wide enough. Try widening it.
Humb13St3psAuthor Commented:
Acknowledged, however when I widen the column to over 100 and it is still reading, "#######" all the way across, then this seems to be a problem.
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
I cleared the format off those cells to see what the result was and they showed a negative. Time cannot be negative. If you don't care about that and just want the difference, you'll have to add an ABS function to your formula:

IF(OR(B26="",B26=F26),"",ABS(...

Don't forget you'll need another closing bracket as well.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this in D2
=IF(OR(B2="",B2=F2),"",MOD(F2+(MATCH(UPPER(E2),{"EST","CST","MST","PST"},0)-1)/24-B2-(MATCH(UPPER(C2),{"EST","CST","MST","PST"},0)-1)/24,1))

Open in new window

and then copy down.
[ fanpages ]IT Services ConsultantCommented:
"########"  usually means that the column isn't wide enough. Try widening it.
Acknowledged, however when I widen the column to over 100 and it is still reading, "#######" all the way across, then this seems to be a problem.

"Hash" symbols ("####") can also indicate a negative date is requested to be displayed; that is, if two dates are used in an arithmetic calculation, & one is being subtracted from the other, the result is less than zero.

This is invalid; hence the notation displayed.
Humb13St3psAuthor Commented:
There were two solutions to this issue. First understanding that the negative number is what was causing the issue, and second, how to deal with incorporating negative number solutions into the formula. Thank you all.
[ fanpages ]IT Services ConsultantCommented:
You are very welcome.

I am glad the issue was (relatively) easy to resolve.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.