calculate date and time

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

I have a database the I inherited and need to start calculating CWT
(Customer Wait Time). the database has two fields "Date Originated" and
"Time Originated" that retrieves current date/time from the system. I have
two more fields "Date Onsite" and "Time Onsite". I cannot figure out how to
calculate the time differance between them.
Any Help
 
First of all, I'd strongly suggest you not store Date and Time separately.
Combine them into a single field: it'll make calculations and look ups
easier. Use the Now function to populate the fields with the current
date/time. (If you have occasion where you only need the date portion, or
you only need the time portion, you can use the DateValue or TimeValue
function on your combined field).

Once you've got them combined, simply use the DateDiff function to determine
the difference between them.

If you cannot (or will not) combined them, note that date/time values are
stored as 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day. That means you
can simply add your date and time fields together to get the correct
timestamp For example, DateDiff("n", [Date Originated] + [Time Originated],
[Date Onsite] + [Time Onsite]) will give you the number of minutes between
the two timestamps.
 
Great, whats the easy way to merge the four field into two fields
[Date Originated] + [Time Originated] = Date/Time Originated
[Date Onsite] + [Time Onsite] = Date/Time Onsite

Thx


Douglas J. Steele said:
First of all, I'd strongly suggest you not store Date and Time separately.
Combine them into a single field: it'll make calculations and look ups
easier. Use the Now function to populate the fields with the current
date/time. (If you have occasion where you only need the date portion, or
you only need the time portion, you can use the DateValue or TimeValue
function on your combined field).

Once you've got them combined, simply use the DateDiff function to
determine the difference between them.

If you cannot (or will not) combined them, note that date/time values are
stored as 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day. That means you
can simply add your date and time fields together to get the correct
timestamp For example, DateDiff("n", [Date Originated] + [Time
Originated], [Date Onsite] + [Time Onsite]) will give you the number of
minutes between the two timestamps.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Robert said:
I have a database the I inherited and need to start calculating CWT
(Customer Wait Time). the database has two fields "Date Originated" and
"Time Originated" that retrieves current date/time from the system. I have
two more fields "Date Onsite" and "Time Onsite". I cannot figure out how
to calculate the time differance between them.
Any Help
Click to expand...
Click to expand...
 
Exactly that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Robert said:
Great, whats the easy way to merge the four field into two fields
[Date Originated] + [Time Originated] = Date/Time Originated
[Date Onsite] + [Time Onsite] = Date/Time Onsite

Thx


Douglas J. Steele said:
First of all, I'd strongly suggest you not store Date and Time
separately. Combine them into a single field: it'll make calculations and
look ups easier. Use the Now function to populate the fields with the
current date/time. (If you have occasion where you only need the date
portion, or you only need the time portion, you can use the DateValue or
TimeValue function on your combined field).

Once you've got them combined, simply use the DateDiff function to
determine the difference between them.

If you cannot (or will not) combined them, note that date/time values are
stored as 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day. That means
you can simply add your date and time fields together to get the correct
timestamp For example, DateDiff("n", [Date Originated] + [Time
Originated], [Date Onsite] + [Time Onsite]) will give you the number of
minutes between the two timestamps.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Robert said:
I have a database the I inherited and need to start calculating CWT
(Customer Wait Time). the database has two fields "Date Originated" and
"Time Originated" that retrieves current date/time from the system. I
have two more fields "Date Onsite" and "Time Onsite". I cannot figure out
how to calculate the time differance between them.
Any Help
Click to expand...
Click to expand...
Click to expand...
 
Back
Top