A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Comparing Stop Watch Times



 
 
Thread Tools Display Modes
  #1  
Old November 10th 16, 01:50 PM
Curtopia Curtopia is offline
Junior Member
 
First recorded activity by ExcelBanter: Nov 2016
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?
Ads
  #2  
Old November 10th 16, 03:05 PM posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
 
Posts: 3,403
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  
Old November 10th 16, 06:59 PM
Curtopia Curtopia is offline
Junior Member
 
First recorded activity by ExcelBanter: Nov 2016
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  
Old November 10th 16, 08:05 PM posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
 
Posts: 3,403
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 03:53 PM.


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