Negative Time Formula

[jayclinton]

I can't use 1904. I have a formula that almost works
I am doing everything in Military time. This is the finial peice of the puzzle.

Our shift begins at 18:00 and ends up until 7:00 (am) or so depending onwork

A1 I have a time (Projected-Estimate of when all work should be done)
A2 I have a time (Actual- Time the work was actually complete.)
A3 Will be show the difference in either a Negative or Positive time.

Three Examples: (The first two work as is and are perfect)

A1 2:11 (Projected)
A2 2:23 (Actual)
A3 0:11 (Difference) 11 min over projected. (Formula below)

=IF(A1 < A2,TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"h:mm"),TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"-h:mm"))


A1 2:23 (Projected)
A2 2:11 (Actual)
A3 -0:11 (Difference) 11min under projected.

So far this is excellent. This issue comes when:

A1 23:22 Projected
A2 1:22 Actual
A3 -22:00 which should be 2:00

I know =mod will work for that case but not if it were 23:00 projected and 22:00 actual.

So with the formula that I am using =IF(A1 < A2,TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"h:mm"),TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"-h:mm"))

How do I get it to tell be the difference while showing a - or + number in military time if it transends the next day? I was trying to add more "if" statements in there but can't get it to work.

Thanks for attempting this if it is possible.

[teylyn]

So far this is excellent. This issue comes when:

A1 23:22 Projected
A2 1:22 Actual
A3 -22:00 which should be 2:00

If A2 means to convey that it's actually later than A1, then you need to incorporate that into A2. How is A2 arrived at? Excel needs to determine that the time in A2 is actually on the following day compared to the time in A1, so you may need to include not just the time but also the date into your calculations.

If you can post an example of your spreadsheet, I'm sure we'll work something out!

cheers

[jayclinton]

I have attached a worksheet of only what I need done. My projected end time comes from a ton of other formulas that takes many factors into account. Then it will give a time in A2. That is all set. Then I would enter the actual time in. This would be when we actually completed all the work. Then C2 will tell me the difference including if it was over or under "x" amount.

I was trying to add in Mod and if's because seperately I can get a Mod to work. Like =Mod((C2-B2),1) would work for one way and b2-c2 works for if the numbers are reversed, but I can't incorporate everything to get it to work. I am not sure if I explained that well enough.

[teylyn]

I think you need to include the date into the equation, to make sure Excel can differentiate that if a time in B is smaller than a time in A, it is on the same day or not.

Then, change your formula in column C to

=IF(A2 < B2,TEXT(MAX(A2:B2)-MIN(A2:B2),"[h]:mm"),TEXT(MAX(A2:B2)-MIN(A2:B2),"-[h]:mm"))

note the [] brackets around "h" which will show hours greater than 24.

See attached, where the values in A and B include dates as well as time.

hth

[contaminated]

U can also use 1904 date system if u want display negative time.

[teylyn]

ContaminatedWit: read the very first sentence of this thread!

[shg]

Another alternative: =DOLLARFR(24*(A2-B2), 60)

To convert back to time, =DOLLARDE(that result, 60) / 24