Calculate difference of two military times and display as + or - format

[Jmoz092]

We have a task (Task2) that begins at a time that we track. We can begin that task before or after a different task (Task1) completes, which we also track. I need to determine the difference in these two times (logged in military time) and then convert that time to hh:mm format in order to determine an available potential for productivity increase; the sooner that task 2 begins, we can do more work that is dependent on both tasks beginning.

i.e.:
if Task 1 completes at 0700, and Task2 begins at 0730, we've lost 0030 (00:30).
if Task 1 completes at 0700 and Task2 begins at 0630, we've gained 0030 (00:30).

I'm using a formula to calculate differences in military time to hh:mm, but I can not figure out how to expand this formula to meet the aforementioned need. Start time is in C4, End time is in D4.

[COLOR=#000000][FONT=&quot]=IF(AND[COLOR=#006107]([/COLOR][COLOR=#0057d6]C4[/COLOR]>0,[COLOR=#006107]D4[/COLOR]>[COLOR=#0057d6]C4[/COLOR][COLOR=#006107])[/COLOR],TEXT[COLOR=#006107](D4[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]-TEXT[COLOR=#006107]([/COLOR][COLOR=#0057d6]C4[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]+[COLOR=#006107]([/COLOR][COLOR=#0057d6]C4[/COLOR]>[COLOR=#006107]D4)[/COLOR],"")[/FONT][/COLOR]

I'm trying to change it to suit my needs, but this isn't working. Task1 time is tracked in N1, Task2 start time is tracked in C3.

[COLOR=#000000][FONT=&quot]=IF([COLOR=#0057d6]N1[/COLOR]>[COLOR=#006107]C3[/COLOR],TEXT[COLOR=#006107]([/COLOR][COLOR=#0057d6]N1[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]-TEXT[COLOR=#006107](C3[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]+[COLOR=#006107](C3[/COLOR]>[COLOR=#0057d6]N1[/COLOR][COLOR=#006107])[/COLOR], TEXT[COLOR=#006107]([/COLOR][COLOR=#0057d6]N1[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]-TEXT[COLOR=#006107]([/COLOR][COLOR=#0057d6]N1[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]+[COLOR=#006107](C3[/COLOR]>[COLOR=#0057d6]N1[/COLOR][COLOR=#006107])[/COLOR])[/FONT][/COLOR]

I'll need the positive difference or negative difference to be displayed in the cell, preferably by worksheet formula. I can do this by conditional formatting, but can not reference the positive or negative value in a summary sheet elsewhere in the workbook if I do that.

 

[Jmoz092]

[COLOR=#000000][FONT='inherit']=IF([COLOR=#0057d6]N1[/COLOR]>[COLOR=#006107]C3[/COLOR],TEXT[COLOR=#006107]([/COLOR][COLOR=#0057d6]N1[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]-TEXT[COLOR=#006107](C3[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]+[COLOR=#006107](C3[/COLOR]>[COLOR=#0057d6]N1[/COLOR][COLOR=#006107])[/COLOR], TEXT[COLOR=#006107]([/COLOR][COLOR=#0057d6]N1[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]-TEXT[COLOR=#006107]([/COLOR][COLOR=#0057d6]N1[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]+[COLOR=#006107](C3[/COLOR]>[COLOR=#0057d6]N1[/COLOR][COLOR=#006107])[/COLOR])[/FONT][/COLOR]

Should read:

[COLOR=#000000][FONT=&quot]=IF([COLOR=#0057d6]N1[/COLOR]>[COLOR=#006107]C3[/COLOR],TEXT[COLOR=#006107]([/COLOR][COLOR=#0057d6]N1[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]-TEXT[COLOR=#006107](C3[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]+[COLOR=#006107](C3[/COLOR]>[COLOR=#0057d6]N1[/COLOR][COLOR=#006107])[/COLOR], TEXT[COLOR=#006107](C3[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]-TEXT[COLOR=#006107]([/COLOR][COLOR=#0057d6]N1[/COLOR],"00\:00"[COLOR=#006107])[/COLOR]+[COLOR=#006107]([/COLOR][COLOR=#0057d6]N1[/COLOR]>[COLOR=#006107]C3)[/COLOR])[/FONT][/COLOR]

apologies

 

[barry houdini]

You can only display negative time in Excel if you switch to 1904 date system (you can do that in Options > Advanced). If you switch for an existing worksheet it will change any dates you already have, so you may not want to do that.

Other options are to show as a negative but as a text value (which you won't be able to easily use in other calculations, or indicating positive or negative in another cell

Will all of these times be on the same day?

 

[Jmoz092]

Interesting.

No this is a daily activity that we need tracked.

I made a workaround of sorts, but I'm interested in your proposal.

 

[Jonmo1]

Here's a couple examples.

Column C is a real time value, always shown in Positive. Column D is the indication whether C positive or negative.

Column H will show the +/- along with the time, but it will be shown as a TEXT string, and won't be useable in further calculations. You can't do H2+H3

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Start	End	As Time	Show + or -		Start	End	As Text
2	1600	1200	04:00	-		1600	1200	-04:00
3	1200	1800	06:00	+		1200	1800	+06:00
Sheet1

Cell Formulas
Range	Formula
C2	=ABS(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"))
C3	=ABS(TEXT(B3,"00\:00")-TEXT(A3,"00\:00"))
D2	=IF(A2>B2,"-","+")
D3	=IF(A3>B3,"-","+")
H2	=IF(F2>G2,"-","+")&TEXT(ABS(TEXT(G2,"00\:00")-TEXT(F2,"00\:00")),"hh:mm")
H3	=IF(F3>G3,"-","+")&TEXT(ABS(TEXT(G3,"00\:00")-TEXT(F3,"00\:00")),"hh:mm")

 

[Jmoz092]

Thank you, that's useful

 

[Jonmo1]

You’re welcome