Elapsed time using date and time fields

[Mary]

I have 4 fields to be included in the elapsed time calculation. It was easier
for the user to have separate fields for Date and Time.

EventStartDate
EventStartTime
EventEndDate
EventEndTime

I need to calculate using DateDiff to see elapsed days, hours and minutes.
I added text boxes for eventstart and eventend that combine the date and time
fields in mm/dd/yy hh:nn format, but I'm not able to calculate using those
fields.

Thanks in advance!

Mary

 

[fredg]

I have 4 fields to be included in the elapsed time calculation. It was easier
for the user to have separate fields for Date and Time.

EventStartDate
EventStartTime
EventEndDate
EventEndTime

I need to calculate using DateDiff to see elapsed days, hours and minutes.
I added text boxes for eventstart and eventend that combine the date and time
fields in mm/dd/yy hh:nn format, but I'm not able to calculate using those
fields.

Thanks in advance!

Mary

Mary,
Your database design is faulty, and you have made it much harder for
yourself than needed.
One field, including the date and time, for [EventStart] and another
field, including date and time for [EventEnd].
Then it's very easy using the Diff2Date function, found here:

http://www.accessmvp.com/djsteele/Diff2Dates.html

to return the days, hours, and minutes in one function

= Diff2Dates("dhn",[EventStart],[EventEnd],False)

would return, for example, 2 days 14 hours 12 minutes.

If your users cannot handle the entry of the date and time within one
date field, use 2 different control's on the data entry form in
addition to the bound [EventStart] and [EventEnd] fields, then add the
time to the date when saving the record data.
Code the Form's BeforeUpdate event:

Me.[EventStart] = Me.[EventStart] + Me.[StartTime]
Me.[EventEnd] = Me.[EventEnd] + Me.[EndTime]

where [EventStart] and [EventEnd] are the names of the fields in the
table and the names of the control on the form in which the user
enters just the date.

[StartTime] and [EndTime] are UNBOUND controls set to a Time format
(i,e, "Medium Time")
Code the Form's Current event:
Me.[StartTime] = Null
Me.[EndTime] = Null
to clear any previous entry in the unbound controls before starting
the next entry.

The user enters the date in the [EventStart] control, then enters the
Time in the [StartTime] control. Do the same with the [EventEnd] and
[EndTime] controls.
When the record is saved the times will be added to the dates in the
appropriate field.

 

[Mary]

I'm using the diff2dates function and changed the design as you suggested
with just two date fields, EventStart and EventEnd.

One more question - is it possible to abbreviate the results of that
function. Example:
3 hours, 15 minutes would be something like 3h, 15m or 3 hrs, 15 min

Thanks again,
Mary

I have 4 fields to be included in the elapsed time calculation. It was easier
for the user to have separate fields for Date and Time.

EventStartDate
EventStartTime
EventEndDate
EventEndTime

I need to calculate using DateDiff to see elapsed days, hours and minutes.
I added text boxes for eventstart and eventend that combine the date and time
fields in mm/dd/yy hh:nn format, but I'm not able to calculate using those
fields.

Thanks in advance!

Mary

Mary,
Your database design is faulty, and you have made it much harder for
yourself than needed.
One field, including the date and time, for [EventStart] and another
field, including date and time for [EventEnd].
Then it's very easy using the Diff2Date function, found here:

http://www.accessmvp.com/djsteele/Diff2Dates.html

to return the days, hours, and minutes in one function

= Diff2Dates("dhn",[EventStart],[EventEnd],False)

would return, for example, 2 days 14 hours 12 minutes.

If your users cannot handle the entry of the date and time within one
date field, use 2 different control's on the data entry form in
addition to the bound [EventStart] and [EventEnd] fields, then add the
time to the date when saving the record data.
Code the Form's BeforeUpdate event:

Me.[EventStart] = Me.[EventStart] + Me.[StartTime]
Me.[EventEnd] = Me.[EventEnd] + Me.[EndTime]

where [EventStart] and [EventEnd] are the names of the fields in the
table and the names of the control on the form in which the user
enters just the date.

[StartTime] and [EndTime] are UNBOUND controls set to a Time format
(i,e, "Medium Time")
Code the Form's Current event:
Me.[StartTime] = Null
Me.[EndTime] = Null
to clear any previous entry in the unbound controls before starting
the next entry.

The user enters the date in the [EventStart] control, then enters the
Time in the [StartTime] control. Do the same with the [EventEnd] and
[EndTime] controls.
When the record is saved the times will be added to the dates in the
appropriate field.

 

[fredg]

I'm using the diff2dates function and changed the design as you suggested
with just two date fields, EventStart and EventEnd.

One more question - is it possible to abbreviate the results of that
function. Example:
3 hours, 15 minutes would be something like 3h, 15m or 3 hrs, 15 min

Thanks again,
Mary

I have 4 fields to be included in the elapsed time calculation. It was easier
for the user to have separate fields for Date and Time.

EventStartDate
EventStartTime
EventEndDate
EventEndTime

I need to calculate using DateDiff to see elapsed days, hours and minutes.
I added text boxes for eventstart and eventend that combine the date and time
fields in mm/dd/yy hh:nn format, but I'm not able to calculate using those
fields.

Thanks in advance!

Mary

Mary,
Your database design is faulty, and you have made it much harder for
yourself than needed.
One field, including the date and time, for [EventStart] and another
field, including date and time for [EventEnd].
Then it's very easy using the Diff2Date function, found here:

http://www.accessmvp.com/djsteele/Diff2Dates.html

to return the days, hours, and minutes in one function

= Diff2Dates("dhn",[EventStart],[EventEnd],False)

would return, for example, 2 days 14 hours 12 minutes.

If your users cannot handle the entry of the date and time within one
date field, use 2 different control's on the data entry form in
addition to the bound [EventStart] and [EventEnd] fields, then add the
time to the date when saving the record data.
Code the Form's BeforeUpdate event:

Me.[EventStart] = Me.[EventStart] + Me.[StartTime]
Me.[EventEnd] = Me.[EventEnd] + Me.[EndTime]

where [EventStart] and [EventEnd] are the names of the fields in the
table and the names of the control on the form in which the user
enters just the date.

[StartTime] and [EndTime] are UNBOUND controls set to a Time format
(i,e, "Medium Time")
Code the Form's Current event:
Me.[StartTime] = Null
Me.[EndTime] = Null
to clear any previous entry in the unbound controls before starting
the next entry.

The user enters the date in the [EventStart] control, then enters the
Time in the [StartTime] control. Do the same with the [EventEnd] and
[EndTime] controls.
When the record is saved the times will be added to the dates in the
appropriate field.

Sure, just change the Diff2Dates code that you down loaded.
Find the following lines:

If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", "
year")
End If

Then change ... IIf(lngDiffYears <>1, "years","year")
to
... IIf(lngDiffYears<>1, "yrs","yr")


Here is a complete example of the hours:

If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffHours & IIf(lngDiffHours <> 1, " hrs", " hr")
End If
End If

Do the same for Years, Months, Days, Hours, Minutes, and Seconds in
the code.

 

[Mary]

Thanks!

I'm using the diff2dates function and changed the design as you suggested
with just two date fields, EventStart and EventEnd.

One more question - is it possible to abbreviate the results of that
function. Example:
3 hours, 15 minutes would be something like 3h, 15m or 3 hrs, 15 min

Thanks again,
Mary

On Fri, 24 Jul 2009 09:56:01 -0700, Mary wrote:

I have 4 fields to be included in the elapsed time calculation. It was easier
for the user to have separate fields for Date and Time.

EventStartDate
EventStartTime
EventEndDate
EventEndTime

I need to calculate using DateDiff to see elapsed days, hours and minutes.
I added text boxes for eventstart and eventend that combine the date and time
fields in mm/dd/yy hh:nn format, but I'm not able to calculate using those
fields.

Thanks in advance!

Mary

Mary,
Your database design is faulty, and you have made it much harder for
yourself than needed.
One field, including the date and time, for [EventStart] and another
field, including date and time for [EventEnd].
Then it's very easy using the Diff2Date function, found here:

http://www.accessmvp.com/djsteele/Diff2Dates.html

to return the days, hours, and minutes in one function

= Diff2Dates("dhn",[EventStart],[EventEnd],False)

would return, for example, 2 days 14 hours 12 minutes.

If your users cannot handle the entry of the date and time within one
date field, use 2 different control's on the data entry form in
addition to the bound [EventStart] and [EventEnd] fields, then add the
time to the date when saving the record data.
Code the Form's BeforeUpdate event:

Me.[EventStart] = Me.[EventStart] + Me.[StartTime]
Me.[EventEnd] = Me.[EventEnd] + Me.[EndTime]

where [EventStart] and [EventEnd] are the names of the fields in the
table and the names of the control on the form in which the user
enters just the date.

[StartTime] and [EndTime] are UNBOUND controls set to a Time format
(i,e, "Medium Time")
Code the Form's Current event:
Me.[StartTime] = Null
Me.[EndTime] = Null
to clear any previous entry in the unbound controls before starting
the next entry.

The user enters the date in the [EventStart] control, then enters the
Time in the [StartTime] control. Do the same with the [EventEnd] and
[EndTime] controls.
When the record is saved the times will be added to the dates in the
appropriate field.

Sure, just change the Diff2Dates code that you down loaded.
Find the following lines:

If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", "
year")
End If

Then change ... IIf(lngDiffYears <>1, "years","year")
to
... IIf(lngDiffYears<>1, "yrs","yr")


Here is a complete example of the hours:

If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffHours & IIf(lngDiffHours <> 1, " hrs", " hr")
End If
End If

Do the same for Years, Months, Days, Hours, Minutes, and Seconds in
the code.