Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Junior Member
 
Posts: 2
Default

Quote:
Originally Posted by Claus Busch View Post
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stop watch time in Excel Cam Excel Discussion (Misc queries) 3 September 9th 07 09:45 AM
Syntax to stop code in Watch Window [email protected] Excel Programming 9 June 4th 07 01:17 PM
Comparing Times in Text Boxes to Times in Cells Matt[_39_] Excel Programming 1 August 6th 06 04:10 AM
can excel be a time clock or stop watch? Anthony Reynard Chinnis New Users to Excel 1 February 15th 06 07:51 AM
Time Display in a Stop Watch fromat Gabe Excel Discussion (Misc queries) 7 December 17th 05 09:11 AM


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

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

About Us

"It's about Microsoft Excel"