Difficult calculating difference in times issue

W

wars0696

New Member
Joined
Aug 19, 2009
Messages
3
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-com:office:office" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
A = 23:34, B = 00:05, C should = 00:31 but instead gives -23:29<o:p></o:p>
<o:p></o:p>
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:p></o:p>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Thanks.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
That works for the example I gave, but not all figures. For example:<o:p></o:p>
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:p></o:p>
 
Upvote 0
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-com:office:office" /><o:p></o:p>
<o:p> </o:p>
<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:p></o:p>
</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:p></o:p>
</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:p></o:p>
</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:p></o:p>
(value of =SUM(B-A)<o:p></o:p>
</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:p></o:p>​
</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:p></o:p>​
</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:p></o:p>​
</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:p></o:p>​
</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:p></o:p>​
</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:p></o:p>​
</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:p></o:p>​
</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:p></o:p>​
</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:p></o:p>​
</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:p></o:p>​
</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:p></o:p>​
</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:p></o:p>​
</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:p></o:p>
<o:p> </o:p>
For the first row =IF(C2<-F1,24-SUM(A2-B2))<o:p></o:p>
<o:p> </o:p>
For the second two rows =IF(D3>F1,-(24+SUM(A3-B3)), D3)<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
Can anyone think of a way I can combine these two formulas?<o:p></o:p>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
248
dshtesy
D
R
  • Question Question
Calculating difference between swim times and swim standards
Replies
1
Views
854
Rodenback
R
I
  • Question Question
Difference between times over midnight
Replies
3
Views
324
ISlo
I
N
  • Question Question
Need Formula help
Replies
5
Views
561
AhoyNC
A
AOB
  • Question Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
671
AOB
AOB

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top