yet another time problem
i give up. read all i can read. tried to find other apps to no avail. so i'll just ask it here. everything works in excel but i'm using an ipad at work and only able to use excel at home.
EXCEL (custom format of [hh]:mm)
1 A B C D E F G
2 05:35 06:19 06:31 19:43 13:12 13:05 +0:44
A, B, C, D = inputs
E > F(X)=IF(C2>D2, (D2-C2)+1, (D2-C2))
F > F(X)=CEILING(E2-"0:10","0:05")
G > F(X)=IF(B2>A2, TEXT(ABS(B2-A2),"+h:mm"), IF(B2<A2, TEXT(ABS(B2-A2), "-h:mm"), "ERROR")) ---> delay or gain (+ is delay, - is gain)
NUMBERS (format of time hh:mm)
E > F(X)=IF(TIMEVALUE(D2)−TIMEVALUE(C2)<0,DURATION(0,0,D2−C2+24,0,0,0),D2−C2)
F doesn't work. I can't think of a way. Any substitute? This is really important. Our way of estimating duration of travel (D-C), which is weird because we need E before we can "estimate".
G > F(X)=TIME(24,0,0)−(B2−A2) but is showing 23:16 (24:00-23:16 = 0:44!!!). Remove "TIME", it shows 968904:44.
- what will happen if B < A? will I get a negative time (which i want to see). also, when D < C (eg. D = 2:20, C = 14:40), it says "Duration units were removed."
Thanks.