Subtracting Time (1 Viewer)

Dreamweaver

Well-known member
Local time
Today, 15:36
Joined
Nov 28, 2005
Messages
2,466
I have this line of code which works fine but I have been trying to subtract 30 minutes for dinner breaks and can't seem to get it working can anyboody point me in the right direction.


Line = Format(Me![TimeStarted] - 1 - Me![TimeFinished], "Short Time")


thanks mick
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:36
Joined
Oct 29, 2018
Messages
21,468
Hi. Just a guess, so you'll need to test it, but maybe try something like:
Code:
Line = Format(Me.![TimeStarted] - 1 - DateAdd("n",-30,Me![TimeFinished]), "Short Time")
Hope it helps...
 

Micron

AWF VIP
Local time
Today, 10:36
Joined
Oct 20, 2018
Messages
3,478
isn't TimeFinished the greater value?

Are you sure you want to subtract greater from lesser?
"isn't working" isn't helping, by the way. That could mean anything.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:36
Joined
Feb 28, 2001
Messages
27,175
The question for time manipulation ALWAYS includes issues of data format.

I'll try to explain. When dealing with a DATE variable, you are actually dealing with something else. Date/time info in Access is stored as a DOUBLE (64-bit floating point) but is "typecast" as DATE. When a date is stored in a DATE variable, you can use addition and subtraction to diddle with the variables. So adding and subtracting times IS quite possible and works fine if you understand what is actually happening.

A date is stored as the number of days and fractions of a day since a reference date. In this case, day 0 is midnight of the last day of 1899 (or close to that). For any date, the calendar date is broken down to the number of days since the reference date as the integer part. That means that Jan 1, 2000 was about 36524 (give or take a couple). The time of day is just the fraction of a day since midnight. (So noon = 0.5, 6 PM = 0.75, etc.)

When you are using "Short Time" format, that implies that you think the thing being formatted is in date format. If those two time entities were in DATE variables, you would be able to do formatting. However, that statement leaves me with a question or two. For instance, that "-1" means you are removing one day from "Time Started" and then subtracting the finish time. A second issue is that normally you consider the finish time to be AFTER the start time - so you subtract Start from Finish. Which means you probably got a negative time and the -1 is an attempt to reverse the numbers again?

I think it would be good if you could first tell us in English what it is that you are trying to do, because I have already seen at least two questionable issues here in the line of code and in your question. DON'T answer with code. Answer with a statement of intent. We can probably help you but we need to know where you are heading.
 

Cronk

Registered User.
Local time
Tomorrow, 00:36
Joined
Jul 4, 2013
Messages
2,772
Like @Doc, I was confused when I say the -1. But it's superfluous


? (#8:00# - (#16:00# - #0:30#)) => 7:30


? (#8:00# -1 - (#16:00# - #0:30#)) => 29/12/1899 7:30:00 AM



? format((#8:00# - (#16:00# - #0:30#)),"Short Time") => 7:30
 

Bullschmidt

Freelance DB Developer
Local time
Today, 09:36
Joined
May 9, 2019
Messages
40
Just to throw in another related approach. In general here is how I often do time difference calculations using the DateDiff() function:

Code:
ElapsedMinutes = DateDiff("N", OlderTime, NewerTime)
 

Users who are viewing this thread

Top Bottom