Date & Time Calculations

  • Thread starter Thread starter Rick Conover
  • Start date Start date
R

Rick Conover

I have a Start Date (mm/dd/yyyy) field and Start Time (hh:nn) field. I also
have an End Date (mm/dd/yyyy) and End Time (hh:nn).

What code can I write in the After Update event procedure of the End Time
that will calculate the total number of hours and minutes between the dates
and times even if it spans into multiple dates.

I have written code that prevents the user from entering any minute interval
other than 00, 15, 30 or 45.

Any help will be greatly appreciated.

Rick
 
Rick said:
I have a Start Date (mm/dd/yyyy) field and Start Time (hh:nn) field. I also
have an End Date (mm/dd/yyyy) and End Time (hh:nn).

What code can I write in the After Update event procedure of the End Time
that will calculate the total number of hours and minutes between the dates
and times even if it spans into multiple dates.

I have written code that prevents the user from entering any minute interval
other than 00, 15, 30 or 45.
Click to expand...


You really shouldn't have separate fields for date and time.
A date/time field will contain both a date and a time no
matter what you do. if the time part is not specified, the
the time part is midnight. If the date part is missing, the
it is 30 Dec 1899. If you have the date time values in just
tow fields, then the difference in minutes would be:
DateDiff("n", startdatetime, enddatetime)

If you are absolutely certain that your existing fields have
the default time and date, then you can cheat by using:
DateDiff("n", startdate + starttime, enddate + endtime)

Beyond that issue, the code in the AfterUpdate (and
Current?) event would look like:

Dim lngMinutes As Long
lngMinutes = DateDiff("n", . . .
Me.txtElapsed = lngMinutes \ 60 & _
Format(lngMinutes Mod 60, "\:00")
 
Marshall,

Like the code above. However, This is my situation: I'
subtracting 3 DateTime for a day then adding those DateTimes together
Since the result is a string, how can we add the result from th
DateTime results
 
bud2148 said:
Like the code above. However, This is my situation: I'm
subtracting 3 DateTime for a day then adding those DateTimes together.
Since the result is a string, how can we add the result from the
DateTime results?
Click to expand...


You lost me. What three date times are wyou talking about?
What does adding them together mean? How are you converting
which redult to a string?
 
I have a Start Date (mm/dd/yyyy) field and Start Time (hh:nn) field. I also
have an End Date (mm/dd/yyyy) and End Time (hh:nn).
Click to expand...

The format is irrelevant. An Access Date/Time field is stored as a
number, a count of days and fractions of a day (times) since midnight,
December 30, 1899; as such, it's usually best to store the date and
time in one field.
What code can I write in the After Update event procedure of the End Time
that will calculate the total number of hours and minutes between the dates
and times even if it spans into multiple dates.
Click to expand...

Use the DateDiff() function. If you want to use the two fields, you'll
have to convert the two fields into one: e.g.

DateDiff("n", CDate([Start Date] & " " & [Start Time]), CDate([End
Date] & " " & [End Time])

This will give you an integer value in minutes. You can use an
expression like

[Duration] \ 60 & Format([Duration] MOD 60, ":00")

to convert this to hh:nn format.
 
Back
Top