UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Time Calculations, And Negative Values!, Access 2016    
 
   
Shanks
post Nov 4 2017, 08:29 PM
Post#1



Posts: 15
Joined: 12-March 13



Hi, I have two fields formatted as Long Time and I need a third calculated field to show the result if I deduct one from the other. The values will be close but may result in either a postive or negative value.

I have tried various versions of:

=[RideTime]-[PBtime]
=Format([pbTime]-[RideTime],"hh:nn:ss")

Each gives a result but i cannot work out how to show a negative result if RideTime is less than PBtime.

Any help or suggestions would be greatly appreciated, Mike
Go to the top of the page
 
MadPiet
post Nov 4 2017, 08:33 PM
Post#2



Posts: 2,262
Joined: 27-February 09



DATEDIFF("n",StartDate,EndDate) ? "n"= minutes. "m" = Months.
Go to the top of the page
 
Shanks
post Nov 4 2017, 08:52 PM
Post#3



Posts: 15
Joined: 12-March 13



Hi Madpiet, thanks for your help.

Interestingly I have tried a similar version and along with yours it just gives me a zero result.

=DateDiff("n",[RideTime],[Pbtime])
Go to the top of the page
 
Shanks
post Nov 4 2017, 09:03 PM
Post#4



Posts: 15
Joined: 12-March 13



I have two lines

RideTime PBtime Result
00:55:48 00:57:20 00:01:32
01:02:00 00:58:20 00:03:40

this is using

=[RideTime]-[Pbtime]

I was hoping the the first result would show -00:01:32

Thanks again for any help
Go to the top of the page
 
RJD
post Nov 4 2017, 10:57 PM
Post#5


UtterAccess VIP
Posts: 7,816
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but this might be a quick fix for you ...

=IIF([PBTime]>[RideTime],"-1","") & Format([pbTime]-[RideTime],"hh:nn:ss")

The problem you are encountering is a classic one - you are calculating ELAPSED time or a TIME DIFFERENCE and formatting it as if it were A POINT IN TIME. These are two different things. And a point in time (time read from a clock) has no native negative indicator.

Most folks I know calculate the difference in seconds (in a case like this), then calculate the hours, minutes and seconds elapsed (or difference) from that, formatting it as ...

xx Hours xx Minutes xx Seconds

...and indicate minuses something like I indicated above. This makes it clear that it is a duration or difference. If you need to sum the values up, have a calculation in seconds (you don't need to display it, just make it available), sum that, and format it the way you want.

See if the separate negative component approach works for you. If not, we will probably need more information from you.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Shanks
post Nov 5 2017, 06:39 PM
Post#6



Posts: 15
Joined: 12-March 13



Hi and thanks for your help and suggestions.

I seem to be going round in circles now. I think I've looked at it for too long and can't see the wood for the trees!

I've attached a sample dbase for you to look at. I have put the calculated fields in the form. The second result is correct as the rideTime was 03:40 longer than the pbtime, but the first result should be a negative result as the ride time was 01:32 quicker.

I really appreciate al the assistance here, Mike

Attached File  SampleData.zip ( 21.81K )Number of downloads: 1
Go to the top of the page
 
RJD
post Nov 5 2017, 07:43 PM
Post#7


UtterAccess VIP
Posts: 7,816
Joined: 25-October 10
From: Gulf South USA


Hi again Mike: Well, this is my fault - in that I inadvertently made the negative "-1" instead of just "-". I obviously mis-typed and did not catch that.

Try this ...

=IIf([PBTime]>[RideTime],"-","") & Format([pbTime]-[RideTime],"hh:nn:ss")

Also see the control I added that shows another format. The format you are using, while you may be comfortable with it, is a point-in-time type format, usually indicating the time-of-day. Just a thought ...

HTH
Joe
Attached File(s)
Attached File  SampleData_Rev1.zip ( 33.67K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Shanks
post Nov 5 2017, 07:51 PM
Post#8



Posts: 15
Joined: 12-March 13



Hi Joe, that is brilliant. I didn't event know you could format a cell in that way. What an improvement.

Thank you very much, now off to spend some time trying to understand it so that I can make the rest of them myself ;-)

Cheers, Mike
Go to the top of the page
 
RJD
post Nov 5 2017, 07:55 PM
Post#9


UtterAccess VIP
Posts: 7,816
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Mike. Glad that was helpful.

Good luck with your project. Let us know if we can be of further assistance.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 11:57 AM