Software

Ask something
Discussion Locked

Date/Time functions in Access

0 helpful
10 replies

by shanghai sam

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a user who needs to find the difference between two dates and times. She has her information in four fields: Approved Date and Approved Time and then Submit Date and Submit Time. She needs to find the difference between the Approved Date/Time and the Submit Date/Time. And she wants the answer to be returned in decimal points. For example: If she has an Approved Date and time of Jan 1 at 3:00pm and an Submit Date and Time of Jan 2 at 10:00am, the difference would be .5 days (figuring an 8 hour day)

Does she need to combine the Submit Date and Submit Time into one field, and the same with Approved Date and Approved Time?

Also, how do you account for weekends?

I hope I've explained this so it's understandable...any help will be greatly appreciated!

10 replies

by c1980

The below function can be added to a module.

Public Function TimeElapsed(dtStartDateTime As Date, dtEndDateTime As Date) As Single
Dim dCalc As Single

'For testing
dtStartDateTime = "01/01/02 12:30:00"
dtEndDateTime = "02/28/02 02:00:00"

If DateValue(dtStartDateTime) > DateValue(dtEndDateTime) Then Exit Function

'Get the difference in days
dCalc = DateDiff("d", dtStartDateTime, dtEndDateTime)

'Subtract 1 day because we only wantwhole days and do not want to include the start and end date
If dCalc > 0 Then dCalc = dCalc - 1

'Now calc the seconds remaining in the first day and return a decimal portion of a day,
'then add the result to the previous calculation
dCalc = dCalc + DateDiff("s", Format$(dtStartDateTime, "hh:mm:ss"), "23:59:59") / 86400

'Now calc the seconds passed in the end day and return a decimal portion of a day,
'then add the result to the previous calculation dCalc = dCalc + DateDiff("s", "00:00:01", Format$(dtEndDateTime, "hh:mm:ss")) / 86400

TimeElapsed = dCalc
End Function

by c1980

Ok. I didn't explain how to pass the seperate dates and times. Better is to have one field for both - the table is smaller then, faster searches, easier to work with. You can combine the fields into a new field in one clean sweep, remove the 2 seperate fields, and change your code.

Anyways, to pass the two seperate fields to the above function you need to combine them into a string as in:
dtStartDateTime = CDate(strStartDate & " " & strStartTime)

Better would be to change the function above and pass variant variables so a string OR date variable can be passed, then of course the first thing that would be checked is if the passed string is a true date:

Public Function TimeElapsed(vStartDateTime As Variant, vEndDateTime As Variant)As Single

Dim dCalc As Single

If Not IsDate(vStartDateTime) Or Not IsDate(vEndDateTime ) Then Exit Function
.
.
.
End Function
Then you could pass the date/time as

dtStartDateTime = CDate(strStartDate & " " & strStartTime)

or
strStartDateTime = strStartDate & " " & strStartTime

by c1980

So now I have re-written the function to take into consideration dates passed as Dates or strings, and allow the caller pass what the first day of week is, and if week days are included or not:

Public Function TimeElapsed(ByVal vStartDateTime As Variant, ByVal vEndDateTime As Variant, _
Optional ByVal bWeekDaysOnly As Boolean = True, _
Optional ByVal iFirstDayOfWeek As VbDayOfWeek = vbUseSystemDayOfWeek) As Single

Dim dCalc As Single
Dim dtTestDate As Date
Dim iLoop As Integer
Dim iTotalDays As Integer
Dim iDayType As VbDayOfWeek

'************************************
'REMOVE- For testing only
'vStartDateTime = "01/01/02" & " " & "12:30:00"
'vEndDateTime = "02/28/02" & "" & "02:00:00"
'************************************

If Not IsDate(vStartDateTime) Or Not IsDate(vEndDateTime) Then Exit Function
If DateValue(vStartDateTime) > DateValue(vEndDateTime) Then Exit Function

'If dates are passed as a string, then take into consideration international formats:
vStartDateTime = CDate(vStartDateTime)
vEndDateTime = CDate(vEndDateTime)

'Get the difference in days
dCalc = DateDiff("d", vStartDateTime, vEndDateTime)

'Subtract 1 day because we only want whole days and do not want to include the start and end date
If dCalc > 0 Then dCalc = dCalc - 1

'If dCalc is still > 0 days then determine how many actual workdays there are if this option is set to TRUE:
If bWeekDaysOnly Then
iTotalDays = dCalc

For iLoop = 1 To iTotalDays
dtTestDate = DateAdd("d", iLoop, vStartDateTime)
iDayType = Weekday(dtTestDate, iFirstDayOfWeek)
If iDayType = vbSaturday Or iDayType = vbSunday Then dCalc = dCalc - 1
Next iLoop
End If

'CONTINUED....

by c1980

'Now calc the seconds remaining in the first day and return a decimal portion of a day,
'then add the result to the previous calculation, taking into consideration the parameter bWeekDaysOnly:
iDayType = Weekday(vStartDateTime, iFirstDayOfWeek)
If Not bWeekDaysOnly Or (iDayType <> iFirstDayOfWeek - 1 And iDayType <> iFirstDayOfWeek - 2) Then
dCalc = dCalc + DateDiff("s", Format$(vStartDateTime, "hh:mm:ss"), "23:59:59") / 86400
End If

'Now calc the seconds passed in the end day and return a decimal portion of a day,
'then add the result to the previous calculation, taking into consideration the parameter bWeekDaysOnly:
iDayType = Weekday(vEndDateTime, iFirstDayOfWeek)
If Not bWeekDaysOnly Or (iDayType <> iFirstDayOfWeek - 1 And iDayType <> iFirstDayOfWeek - 2) Then
dCalc = dCalc + DateDiff("s", "00:00:01", Format$(vEndDateTime, "hh:mm:ss")) / 86400
End If

TimeElapsed = dCalc
End Function

by bob sellman

The separate fields for the dates and times are fine. The following code accounts for weekends and assumes an 8 hour weekday with no lunch. (If you want to exclude lunch time, then you must add code similar in concept to the way I account for weekends, but checking if the time period overlaps lunch.)

This is your criteria:
1. Weeks are 5 days and weekends are excluded.
2. Workdays are 8 hours with fixed starting and ending times.

The code below has on a form the controls txtThisDateand txt.ThisTime for the starting date and time, and txtEndDate and txtEndTime for the ending date and time. txtDays is the control which holds the result. This code could be placed in the on update property of the last data control in which data will be entered. It assumes dates and times entered are valid.

Dim lngDays As Long
Dim lngPartWeekDays As Long
Dim lngStartDay As Long
Dim lngMinutes As Long
Dim lngStartAdjust As Long
' calculate actual number of days difference for dates
lngDays = DateDiff("d", Me.txtThisDate, Me.txtEndDate, vbMonday)
' calculate remainder of days that is a partial week
lngPartWeekDays = lngDays - 7 * Int(lngDays / 7)
' determine day of week for starting date
lngStartDay = WeekDay(Me.txtThisDate, vbMonday)
' if partial week includes weekend, subtract 2 from partial weekdays count
If lngStartDay > 1 Then
If lngPartWeekDays + lngStartDay > 5 Then
lngPartWeekDays = lngPartWeekDays - 2 End If
End If
If Me.txtThisTime > Me.txtEndTime Then
lngMinutes = -1 * DateDiff("n", Me.txtEndTime, Me.txtThisTime)
Else
lngMinutes = DateDiff("n", Me.txtThisTime, Me.txtEndTime)
End If
' Calculate actual number of days based on 5 day weeks (Sat/Sun off) and 8 hour day (480 minutes)
Me.txtDays = lngPartWeekDays + 5 * Int(lngDays / 7) + lngMinutes / 480

by bob sellman

I tried testing this code before posting it and it worked even for a three week period. The assumption is that the first day of work is Monday, thus the line

lngStartDay=WeekDay(me.txtThisDate,vbMonday)

If your work week starts on a day otherthan Monday, change the parameter vbMonday to the value for the appropriate day. However, this opens a Pandora's box, since then you must schedule starting and ending dates based on individual employee's schedules.

The easiest way to test the posted function is to create a form with five controls. The first four listed should have the appropriate columns in your table as their record sources.:

txtThisDate contains the start date
txtThisTime contains the start time
txtEndDate contains the ending date
txtEndTime contains the ending time
txtDays contains the days in decimals

For data entry you can post the code in the onUpdate property of txtEndTime (assuming that is the last of the four data fields in which data will be entered).

If you want to display the value for existing records, also add the same code to the OnCurrent property of the form.

If it doesn't work for you, let me know what problem you are encountering.

by bob sellman

My comment about the different starting day for work opening a Pandora's box only applies if not all employees work the same schedule.

by sl-campbell

I have to say that I couldn't get any of the above answers to work 100%(like if the parameter iFirstDayOfWeek is used, or if the time span between the two dates are more than 2 weeks, etc). Probably because a 100% testing wasn't done, but the answers are still worthy of respect and are great to build upon- they just need some modifications. And I could use a function similar to this so I will try to improve it - leaving room for someone else to come in and improving it even more:

First of all with answer #2 the 3 identical lines:
iDayType = Weekday(dtTestDate, iFirstDayOfWeek)
MUST be changed to the following(iFirstDayOfWeek to vbSunday):

iDayType = Weekday(dtTestDate, vbSunday)

Because the vbDayOfWeek constants ALWAYS have the same value, then when using the WeekDay function to return the day of the week, the first day needs to begin with the constant that has the lowest value = vbSunday. If you do not do it that way, and allow the user to change it with the variable iFirstDayOfWeek, you will run into problems, with-out adjusting the values of the vbDayOfWeek Constants by creating a variable for each day of the week, assigning them the day constants repectively, and using iFirstDayOfWeek as an offset. To make things easier, leave it as vbSunday. Besides, the WORK day is sought here and NOT the day of week - 2 different things.
And because of that, the 2 lines:

If Not bWeekDaysOnly Or (iDayType <> iFirstDayOfWeek - 1 And iDayType <> iFirstDayOfWeek - 2) Then
will need to be changed. They do not work anyways because iFirstDayOfWeek - 1 may be -1 and not 7:

If Not bWeekDaysOnly Or (iDayType <> vbSaturday And iDayType <> vbSunday- 2) Then

by sl-campbell

You might want to add an optional parameter like bSaturdayIsWorkDay As Boolean for those cases, or even a ParamArray to let the user pass the days NOT to include in the calculation. There are more things that could be added like bWholeDaysOnly As Boolean which comes in handy (as opposed to the basic DateDiff function) when you want to exclude weekends or certain days.

This discussion is closed to new replies.