Subtracting Date & Time

[AH]

Hi all,

This is a simple question...I hope. How can I subtract
time in a query. For example, if I have this column with
employee entry times at 8:00 a.m. and I get employee 1
entering at 8:25 a.m. I want a query field to subtract
8:00 from 8:25 to get 25min. late in the query.

I am entering this for the field:

Late: Time!morningin-#8:00:00 AM# but it gives me this
weird number 0.0104166666666667 !! What's up with that?!
Please help...thanks,

 

[JohnFol]

Nothing is up, just the field has not been formatted as Time

 

[Cheryl Fischer]

The DateDiff() function should take care of that for you:

DateDiff("n",#8:00:00 AM#, Time!morningin)

 

[ExcelAid.com]

There are loads of ways of doing this... Firstly, the
number you are getting (0.0104....) is microsoft code for
a time.

Simply format this result as hh:mm:ss and it should show
up as 25mins.

i.e Late: format(Time!morningin-#8:00:00 AM#,"hh:mm:ss")

Should work like a dream

ExcelAid

 

[Dale Fye]

As indicated by Cheryl, datediff() is the function you want. As I
recall, there are reasons not to do the arithmatic addition and
subtraction of dates and times, best to use the built-in functions
dataadd() and datediff().

However, it would be beneficial for you to know that the .0104 is not
a "Microsoft code". It is a representation of the percentage of a day
represented by the time.

Access stores date/time values as double precision numbers, where the
integer portion of the value represents the number of days that have
transpired since some date (I think it is 30 Dec, 1899 but don't
quote me on that). The decimal portion is the percentage of the day
completed (12 Noon would be 12/24 = .5, 6 AM would be 6/24 = .25).
Therefore 25 minutes would be represented by 25/60/24 =
..01736111....., not .01046666666667 (which is 15 minutes, 4 seconds).

--
HTH

Dale Fye


Hi all,

This is a simple question...I hope. How can I subtract
time in a query. For example, if I have this column with
employee entry times at 8:00 a.m. and I get employee 1
entering at 8:25 a.m. I want a query field to subtract
8:00 from 8:25 to get 25min. late in the query.

I am entering this for the field:

Late: Time!morningin-#8:00:00 AM# but it gives me this
weird number 0.0104166666666667 !! What's up with that?!
Please help...thanks,

 

[AH]

Cheryl,

That works just as good as the above recommendation with
the exception of formatting the result to hh:mm:ss, but
if it is the proper way, I will stick to it. Thanks,