Difficult calculating difference in times issue

[wars0696]

I am having problems with excel taking one column of time away from another, lets call them A and B (each row is taken from the same occasion). I want to find the difference between A and B (either of which might be the later time) and I want that given in time as either a positive or negative figure. In column C I'm using a basic formal '=SUM(B2-A2)'<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
At first it wouldn't give negative time values (just #######) but I then found the 1904 date setting. However now I find some values which cross midnight are wrong. For example:<o></o>
<o></o>
A = 23:34, B = 00:05, C should = 00:31 but instead gives -23:29<o></o>
<o></o>
I have many thousands of entries and can't start putting dates in with the times. Can anyone please suggest a way around my problem? Really need help on this one!<o></o>

 

[VoG]

Hello and welcome to MrExcel.

Try

=MOD(B1-A1,1)

 

[wars0696]

Thanks.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
That works for the example I gave, but not all figures. For example:<o></o>
A = 01:00, B = 00:40 previously gave the correct answer -00:20 but now with your formal gives 23:40. Odd. What's the formula meant to do?<o></o>

 

[wars0696]

I’ve almost solved my problem. There are three main examples of case for values of A and B below and their correct solutions.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 231pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" cellSpacing=0 cellPadding=0 width=308 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=89>A<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=91>B<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>C (should be)<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>D<o></o>
(value of =SUM(B-A)<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=89>

23:34<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=91>

00:05<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>

00:31<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>

-23:29<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=89>

01:00<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=91>

00:40<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>

23:40<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>

-00:20<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=89>

00:50<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=91>

23:50<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>

23:00<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>

23:00<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4; mso-row-margin-right: 48.0pt"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=89></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=91></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent; mso-cell-special: placeholder" width=64>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5; mso-row-margin-right: 48.0pt"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=89></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=91></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent; mso-cell-special: placeholder" width=64>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6; mso-row-margin-right: 48.0pt"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=89></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=91></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent; mso-cell-special: placeholder" width=64>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7; mso-row-margin-right: 48.0pt; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=89></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=91></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent; mso-cell-special: placeholder" width=64>
</TD></TR></TBODY></TABLE>
Using a new column E and cell F1 with the value 10:00 (Note difference between times A and B will always be under 10 hours) I have been able to gain the correct answer with two formula’s<o></o>
<o> </o>
For the first row =IF(C2<-F1,24-SUM(A2-B2))<o></o>
<o> </o>
For the second two rows =IF(D3>F1,-(24+SUM(A3-B3)), D3)<o></o>
<o> </o>
<o> </o>
<o> </o>
Can anyone think of a way I can combine these two formulas?<o></o>