Remember Me?

#### Menu

#1
November 10th 16, 12:50 PM
 Junior Member First recorded activity by ExcelBanter: Nov 2016 Posts: 2
Comparing Stop Watch Times

I am trying to compare stop watch times against a control time which is a touch pad time.

I populated the touch pad times along with the stop watch times and formatted the times using the custom format mm:ss.00

I then created a simple formula that subtracts the stop watch time from the touch pad time to determine the delta.

The problem: If the delta is either zero (same times) or positive, it works. If the delta is negative, it does not work, just shows up as #####.

Example:
1:05.00 1:04.00 =(A-B) 00:01.00 works
1:05.00 1:05.00 =(A-B) 00:00.00 works
1:05:00 1:06.00 =(A-B) ###### Does not work regardless of the column width

Any thoughts?

#2
November 10th 16, 02:05 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,480
Comparing Stop Watch Times

Hi,

Am Thu, 10 Nov 2016 12:50:16 +0000 schrieb Curtopia:

Example:
1:05.00 1:04.00 =(A-B) 00:01.00
works
1:05.00 1:05.00 =(A-B) 00:00.00
works
1:05:00 1:06.00 =(A-B) ###### Does not
work regardless of the column width

if you only want to display the delte try:
=IF(A1=B1;A1-B1;"- "&TEXT(B1-A1;"mm:ss,00"))

If you want to calculate with the delta:
Excel options = Advanced = When calculating this workbook and activate
"Use 1904 date system"

Regards
Claus B.
--
Windows10
Office 2016
#3
November 10th 16, 05:59 PM
 Junior Member First recorded activity by ExcelBanter: Nov 2016 Posts: 2

Quote:
 Originally Posted by Claus Busch Hi, Am Thu, 10 Nov 2016 12:50:16 +0000 schrieb Curtopia: Example: 1:05.00 1:04.00 =(A-B) 00:01.00 works 1:05.00 1:05.00 =(A-B) 00:00.00 works 1:05:00 1:06.00 =(A-B) ###### Does not work regardless of the column width if you only want to display the delte try: =IF(A1=B1;A1-B1;"- "&TEXT(B1-A1;"mm:ss,00")) If you want to calculate with the delta: Excel options = Advanced = When calculating this workbook and activate "Use 1904 date system" Regards Claus B. -- Windows10 Office 2016
Thank you Claus...

I could not get the formula to work but I have determined that even though a negative delta displays as #######, the cell is holding the correct delta so when I add all the deltas and average them, it is accurate. It would be helpful to display the negative like it does the = or + so if you have any other thoughts, let me know. Thanks again for taking the time to help.
#4
November 10th 16, 07:05 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,480
Comparing Stop Watch Times

Hi,

Am Thu, 10 Nov 2016 17:59:31 +0000 schrieb Curtopia:

=IF(A1=B1;A1-B1;"- "&TEXT(B1-A1;"mm:ss,00"))

I could not get the formula to work but I have determined that even
though a negative delta displays as #######, the cell is holding the
correct delta so when I add all the deltas and average them, it is
accurate. It would be helpful to display the negative like it does the
= or + so if you have any other thoughts, let me know. Thanks again for
taking the time to help.

sorry, the format I wrote is a german format. Try:
=IF(A1=B1;A1-B1;"- "&TEXT(B1-A1;"mm:ss.00"))

Or have a look:
https://1drv.ms/x/s!AqMiGBK2qniT_UyWDLtfoMvGp3Ir

Regards
Claus B.
--
Windows10
Office 2016

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Cam Excel Discussion (Misc queries) 3 September 9th 07 09:45 AM [email protected] Excel Programming 9 June 4th 07 01:17 PM Matt[_39_] Excel Programming 1 August 6th 06 04:10 AM Anthony Reynard Chinnis New Users to Excel 1 February 15th 06 06:51 AM Gabe Excel Discussion (Misc queries) 7 December 17th 05 08:11 AM

All times are GMT +1. The time now is 01:47 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright ©2004-2017 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017