Identifying Days per month from Date Range (1 Viewer)

Fozi

Registered User.
Local time
Today, 17:55
Joined
Feb 28, 2006
Messages
137
Working on an absence database. Start Date and End dates are recorded. From this I then calculate the number of days absent. However when reporting I need to display absent days per month.

So if Joe is off from 27/07/12 thru to 04/08/12 I need it to show x days in July and y days in August.

Any help would be gratefully received.

Fozi
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:55
Joined
Jan 23, 2006
Messages
15,364
Are you using Calendar days or Working Days?

In general the start of a month is day 1 of the month ---- 1/month/year
the end of a month is 1 day less than the start of a month
 

Fozi

Registered User.
Local time
Today, 17:55
Joined
Feb 28, 2006
Messages
137
Are you using Calendar days or Working Days?

In general the start of a month is day 1 of the month ---- 1/month/year
the end of a month is 1 day less than the start of a month

Captures both calendar and working days. So from a 12 day range I want to automatically identify the days for each month.

Thanks for your reply.
 

recyan

Registered User.
Local time
Today, 23:25
Joined
Dec 30, 2011
Messages
180
In general the start of a month is day 1 of the month ---- 1/month/year
the end of a month is 1 day less than the start of a month

Thinking along jdraw's suggestion, just check out if below gives some idea :

Code:
SELECT 
	myTable.EmpID, 
	myTable.AbsentStartDate, 
	myTable.AbsentEndDate, 
	Month([AbsentStartDate]) AS MontOfAbsentStartDate, 
	Month([AbsentEndDate]) AS MontOfAbsentEndDate, 
	DateSerial(Year([AbsentEndDate]),Month([AbsentEndDate]),1) AS 1stDateOfEndMonth, 
        IIf([MontOfAbsentStartDate]=[MontOfAbsentEndDate],DateDiff("d",[AbsentStartDate],[AbsentEndDate]),(DateDiff("d",[AbsentStartDate],[1stDateOfEndMonth])-1)) AS NoOffDaysForStartMonth,
	IIf([MontOfAbsentStartDate]=[MontOfAbsentEndDate],0,DateDiff("d",[1stDateOfEndMonth],[AbsentEndDate])+1) AS NoOffDaysForEndMonth
FROM 
	myTable;

Assumption :
The absence is across maximum of 2 months only ( feel, we should work around this limitation to avoid problems in the long run ).
Note :
Year should also be considered along with month.

Thanks
 
Last edited:

Fozi

Registered User.
Local time
Today, 17:55
Joined
Feb 28, 2006
Messages
137
Thinking along jdraw's suggestion, just check out if below gives some idea :

Code:
SELECT 
	myTable.EmpID, 
	myTable.AbsentStartDate, 
	myTable.AbsentEndDate, 
	Month([AbsentStartDate]) AS MontOfAbsentStartDate, 
	Month([AbsentEndDate]) AS MontOfAbsentEndDate, 
	DateSerial(Year([AbsentEndDate]),Month([AbsentEndDate]),1) AS 1stDateOfEndMonth, 
        IIf([MontOfAbsentStartDate]=[MontOfAbsentEndDate],DateDiff("d",[AbsentStartDate],[AbsentEndDate]),(DateDiff("d",[AbsentStartDate],[1stDateOfEndMonth])-1)) AS NoOffDaysForStartMonth,
	IIf([MontOfAbsentStartDate]=[MontOfAbsentEndDate],0,DateDiff("d",[1stDateOfEndMonth],[AbsentEndDate])+1) AS NoOffDaysForEndMonth
FROM 
	myTable;

Assumption :
The absence is across maximum of 2 months only ( feel, we should work around this limitation to avoid problems in the long run ).
Note :
Year should also be considered along with month.

Thanks

Brilliant. I had to make a couple of adjustments to the code but essentially that's worked well. Only one issue. We only work Mon-Fri, so I need to exclude weekend days.

Once again grateful for your assistance.

Fozi
 

recyan

Registered User.
Local time
Today, 23:25
Joined
Dec 30, 2011
Messages
180
That complicates things a bit.
Again jdraw has already given us the hint "Working Days".

We have a
AbsentStartDate
AbsentEndDate

Possible Cases :
1) Both the dates lie within the same month
2) The StartDate & EndDate are in different months

Case 1 :
We can use the Working day function directly.

Case 2 (This has to be handled differently, as we need the absent days across 2 different months):
a) Find the Last date of the AbsentStartDate Month using the 1st Date of the AbsentEndDate.
b) Find the working days between AbsentStartDate & the Last date of the AbsentStartDate Month
c) Then, using the 1st Date of the AbsentEndDate, Find the working days between the 1st Date of the AbsentEndDate & the AbsentEndDate

Perhaps, below could give you some guidelines :

http://msdn.microsoft.com/en-us/library/office/dd327646(v=office.12).aspx

Note : There might be a simpler alternative.

Thanks
 

Fozi

Registered User.
Local time
Today, 17:55
Joined
Feb 28, 2006
Messages
137
Thanks Recyan

I follow the logic but no sure how to factor in the 'working days' part into the formulae.

Fozi
 

Fozi

Registered User.
Local time
Today, 17:55
Joined
Feb 28, 2006
Messages
137
Thanks but not quite what I'm after. Essentially all periods of sickness are recorded by two dates [Start_Date] and [End_Date]. I need to do two things from here.

1. Extract the weekend days from the range
2. From the remaining days, identify how many fell into each month.

Eg:
Joe off from 26/07/12 to 03/08/12 (inclusive) = 9 days incl weekend.
Extracting weekend = 7 days
By respective month: July: 4 days, August 3 days.

Thanks for any assistance folks.

Fozi
 

recyan

Registered User.
Local time
Today, 23:25
Joined
Dec 30, 2011
Messages
180
Just check out if below gives some guidelines :

To take care of Public Holidays also,
There is an holidays table tblHolidays, which has the Public Holidays listed
tblHolidays -
HolidayID - PK - Autonumber,
HolidayDate - Unique - Date Field,
HolidayName - Text Field


Save below function :

PHP:
Function getWorkingDays(TheStartDate, TheEndDate)

    Dim StartDate As Date
    Dim EndDate As Date
    Dim NoOffCalendarDays As Integer
    Dim NoOffWorkingDays As Integer
    
    StartDate = TheStartDate
    EndDate = TheEndDate
  
    'Assumption : There is an holidays table tblHolidays, which has the Public Holidays listed
    ' tblHolidays -
    ' HolidayID - PK - Autonumber,
    ' HolidayDate - Unique - Date Field,
    ' HolidayName - Text Field
    
    ' Get the number of calendar days between the StartDate & the EndDate
    NoOffCalendarDays = DateDiff("d", StartDate, EndDate) + 1
    NoOffWorkingDays = NoOffCalendarDays
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strsql As String
    
    ' set the current database
    Set dbs = CurrentDb()
    
    Do While StartDate <= EndDate
        ' check if the Date is a Saturday or Sunday
        If Weekday(StartDate) = 7 Or Weekday(StartDate) = 1 Then
            NoOffWorkingDays = NoOffWorkingDays - 1
        
            ' Next, we check if the Date is a Public Holiday
            ' We are checking this in the Else, since if the Date is already a weekend Date,
            ' then we need not check for Public Holiday, as the Date has already been accounted for.
        Else
            ' check if the Date is a Public Holiday & exists in the Holidays Table
            strsql = "SELECT COUNT(*) AS HolidayCount FROM tblHolidays WHERE HolidayDate = #" & StartDate & "# "
            Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
            
            HolidayCountA = rst!HolidayCount
            If HolidayCountA = 1 Then
                NoOffWorkingDays = NoOffWorkingDays - 1
            End If
        End If
        
        StartDate = DateAdd("d", 1, StartDate)
        
    Loop
    
    getWorkingDays = NoOffWorkingDays


End Function

The query :
Code:
SELECT 
	myTable.EmpID, 
	myTable.AbsentStartDate, 
	myTable.AbsentEndDate, 
	Month([AbsentStartDate]) AS MontOfAbsentStartDate, 
	Month([AbsentEndDate]) AS MontOfAbsentEndDate, 
	DateSerial(Year([AbsentEndDate]),Month([AbsentEndDate]),1) AS 1stDateOfEndMonth, 
	IIf([MontOfAbsentStartDate]=[MontOfAbsentEndDate],[AbsentEndDate],DateAdd("d",-1,[1stDateOfEndMonth])) AS EndDateOfStartMonth,
	IIf([MontOfAbsentStartDate]=[MontOfAbsentEndDate],getWorkingDays([AbsentStartDate],[AbsentEndDate]),getWorkingDays([AbsentStartDate],[EndDateOfStartMonth])) AS NoOffWorkingDaysAbsentInStartMonth, 
	IIf([MontOfAbsentStartDate]<>[MontOfAbsentEndDate],getWorkingDays([1stDateOfEndMonth],[AbsentEndDate]),0) AS NoOffWorkingDaysAbsentInEndMonth
FROM 
	myTable;

Note :
1) My signature.
2) We might be re-inventing the wheel.

Thanks
 

Fozi

Registered User.
Local time
Today, 17:55
Joined
Feb 28, 2006
Messages
137
Thanks reycan.

As usual with these things, one step forward etc. That worked a treat so long as there's only one month between start and end date. On a rare occasion long term sick stakk can be off on dates spanning three months.

Is there a way to 'loop' the check returning values identified against each month?

Thanks again for your perseverance

Fozi
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:55
Joined
Jan 23, 2006
Messages
15,364
You could check Month (StartDate) and Month(EndDate) and see what the difference in Months is. Then set up a Select Case or series of IFs to deal with the specifics.
 
Last edited:

recyan

Registered User.
Local time
Today, 23:25
Joined
Dec 30, 2011
Messages
180
In addition to jdraw's suggestion, perhaps, another alternative,

Assumption :
1) A tbltemp table exists or is created on the fly in the VBA code.
2) If the tblTemp exists, the code will delete all the records present in it before proceeding

The pseudo VBA function to poulate the tblTemp :

PHP:
The Function () 
	strsql = "select EmpID, AbsenceStartDate, AbsenceEndDate, from tblEmployeeAbsenceRecords"

	For each Record
		AbsenceStartDate
		AbsenceEndDate
	
		while AbsenceStartDate <= AbsenceEndDate 
			' check if the Date is not a working day or a public holiday
			if it is not a Saturday or Sunday 
				if it is not a public holiday 
					insert in to tbltemp the Date & the Emp Absence Record
				end if
			end if	
			
			AbsenceStartDate = DateAdd("d", 1, AbsenceStartDate) 

		Loop
	Loop
End Function

We can perhaps now use a query, to query the tblTemp, to get the data in whichever way we want.

Thanks
 

Users who are viewing this thread

Top Bottom