Time/Custom formula and formats not responding

[Shu of AZ]

What I'm trying to accomplish is have Col F be the result of subtracting B
time from any time in C, D, or E if populated to find time elasped (F).
There is always two time fields in each row and always in B but as shown, not
always in C, D, or E. I would like one formula to copy into F after I insert
the column.

The formats do not change when I change the in B,C,D,or E. HH:MM AM/PM
also, as I do not need seconds. When the data comes in, it comes in as
General for all fields where time is involved and is exactly as shown in the
example.

Thanks in advance.


a b c d e
f
1 9:44:00 AM 9:44:00 AM #VALUE!
2 9:44:00 AM 9:46:00 AM #VALUE!
3 9:46:00 AM 9:48:00 AM
4 9:46:00 AM 9:48:00 AM
5 9:46:00 AM 9:59:00 AM
6 9:46:00 AM 10:03:00 AM
7 10:41:00 AM 10:50:00 AM
8 10:41:00 AM 10:50:00 AM
9 10:41:00 AM 11:25:00 AM

 

[jatman]

start of by formatting your cells as follows:

columns b, c, d, and e - custom format h:mm am/pm (this part looks ok on
your example.

format column f - custom format [h]:mm

use the following formulae in column f

=IF(AND(C2="",D2=""),E2-B2,IF(AND(C2="",E2=""),D2-B2,C2-B2))

assuming that there is only time in columns b and one of the columns c, d,
or e, you will get your time difference in hours:minutes in column F.

jat