asked on

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

Martin Liss

"########" usually means that the column isn't wide enough. Try widening it.

Martin

ASKER

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.

ASKER CERTIFIED SOLUTION

Brian B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

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.

SOLUTION

[ fanpages ]