Can Access work out how many days into next month

[WorkRelated]

I have a database that keeps records of all the fitting jobs that are
compeleted by team members, each job has a start date and end date. I want
access to tell me if a job runs from one month to the next and if so how many
days? The reason I need this information is to caculate how much the work in
progess figure is. If a job starts on 28th August and finish on 2nd September
I want the value of 2 to be returned as we have spent 2 working days in
September on the job. Please help if you can?

 

[kc-mass]

=Day([FinishDate])

Or in query

SELECT FinishDate, Day([FinishDate]) AS DaysInFinishMonth
FROM tblYourJobs;

Regards

Kevin

 

[Kipp Woodard]

These two functions together can do what you want.

- It assumes Monday-Friday workdays.
- It is inclusive of the date of the EndDate.
- Returns 0 if EndDate < StartDate.
- Returns 0 if EndDate is same month as StartDate.
- You also find uses for WorkDaysBetweenDates() by itself.

Usage:

WorkDaysInSubsequentMonths(#10/1/2009#, #9/30/2009#)

or:
WorkDaysInSubsequentMonths(Job_Start_Date, Job_End_Date)


Begin Code (Paste into a Module)
====================
Option Compare Database
Option Explicit

Const MOD_NAME As String = "modMain"

Public Function WorkDaysBetweenDates(StartDate As Date, EndDate As Date) As
Integer
Const PROC_NAME As String = "WorkDaysBetweenDates"

Dim dtCurrentDate As Date
Dim iDaysBetweenDates As Integer
Dim iResult As Integer
Dim iDayOfWeek As Integer
Dim iDayCounter As Integer

On Error GoTo ErrorHandler

' Determine the number of days between the two dates.
iDaysBetweenDates = DateDiff("d", StartDate, EndDate)

' Loop for the number of days, + 1
For iDayCounter = 0 To iDaysBetweenDates
' Get the date, per the day counter.
dtCurrentDate = DateAdd("d", iDayCounter, StartDate)

' Get the day-of-the-week of the current date.
iDayOfWeek = DatePart("w", dtCurrentDate, vbSunday)

' If it is a work-day...
If iDayOfWeek > 1 And iDayOfWeek < 7 Then
' Count it.
iResult = iResult + 1
End If
Next

Cleanup:
' Assign the result.
WorkDaysBetweenDates = iResult

Exit Function

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , MOD_NAME & "."
& PROC_NAME

On Error Resume Next

GoTo Cleanup

End Function

Public Function WorkDaysInSubsequentMonths(StartDate As Date, EndDate As
Date) As Integer
Const PROC_NAME As String = "WorkDaysInSubsequentMonths"

Dim dtFirstDayOfNextMonth As Date
Dim iResult As Integer

On Error GoTo ErrorHandler

' If the start-date and end-date are in the same month...
If Month(StartDate) = Month(EndDate) And Year(StartDate) = Year(EndDate)
Then
' Nothing to do. Return 0.
GoTo Cleanup
End If

' Add one Month to the start date (automatically handles months that
don't have the same number
' of days, like Jan 31 and Feb 28.
dtFirstDayOfNextMonth = DateAdd("m", 1, StartDate)

' Get the first day of the next month.
dtFirstDayOfNextMonth = CDate(Month(dtFirstDayOfNextMonth) & "/1/" &
Year(dtFirstDayOfNextMonth))

' Get the work-days between the first day of the subsequent month and
the specified end-date.
iResult = WorkDaysBetweenDates(dtFirstDayOfNextMonth, EndDate)

Cleanup:
' Assign the result.
WorkDaysInSubsequentMonths = iResult

Exit Function

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , MOD_NAME & "."
& PROC_NAME

On Error Resume Next

GoTo Cleanup

End Function

====================
End Code

 

[George]

Is your requirement that you need to know the elapsed working days between
the start and end date, or simply that you need to know how many days into
the new month the work continues?

Your example suggests it is actually the former, and that you need to
account for both weekends and holidays.

Here is a link to a solution to that problem:
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1349593


George

 

[WorkRelated]

It is the number of working days ie (monday - friday) that it goes into the
new month.

 

[George Hepworth]

You can use Brent's solution then. Set the "Start Date" to the first day of
the new month and the "End Date" to your "finish date". The function will
return the number of working days in between. Note that you DO need to
create the holiday table specific to your organization.

There is also the issue of determining the first day of the new month, which
is a fairly typical requirement. Here are a couple of functions from MS to
calculate the first day or the last day of a given month.

dhFirstDayInMonth(Optional dtmDate As Date = 0) As Date
' Return the first day in the specified month.
If dtmDate = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If
dhFirstDayInMonth = DateSerial(Year(dtmDate), _
Month(dtmDate), 1)
End Function
Function dhLastDayInMonth(Optional dtmDate As Date = 0) As Date
' Return the last day in the specified month.
If dtmDate = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If
dhLastDayInMonth = DateSerial(Year(dtmDate), _
Month(dtmDate) + 1, 0)
End Function