I know this topic is a veritable dead horse, but I have to ask, because I am unable to find something that is close to my scenario that I can completely understand.
I have a start date field and complete date field.
I need to know how many days, minus holidays and weekends are between the two dates. i already have the table of holidays named "holiday table" with the field being named "Holidaydate".
I need this to output a number into a field for number of days.
Is there a simple way to do this? Most of the code I have seen is a bit beyond my understanding as I am still a novice.
Does anyone have some code that might do this?
8 25443
Here's how I'd subtract the holidays, though I haven't looked at any other posts so there may well be a better way:
listbox.RowSource = "SELECT Holidaydate FROM HolidayTable WHERE Holidaydate BETWEEN #" & StartDate & "# AND #" & EndDate & "#;"
numberOfHolidaysToSubtract = listbox.ListCount
Hi,
I wrote this little function and it seems to do what you want. It is fairly customisable so you should be able to tweak it for your needs. It assumes you have a table somewhere with dates to exclude.
If you are using it in a query, then use it like: - Somefield: Workingdays([Startfield],[Completefield])
If on a form, place an unbound textbox there and then put this in the form's On Current event: -
'Change all control names to your control names.
-
If isnull(Me.StartDateControlName) or isnull(Me.EndDateControlName) Then
-
Me.TextBoxName = "N/A"
-
Exit Sub
-
Else
-
Me.TextBoxName = WorkingDays(Me.StartDateControlName,Me.EndDateControlName)
-
End If
Place this function inside a module. - Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
-
On Error GoTo Err_WorkingDays
-
-
Dim intCount As Integer
-
Dim temp As Integer
-
Dim strWhere As String
-
-
'Set same day as 1 <-- your preference. Though you can't work out averages if they are 0.
-
'Im not checking if this day is a holiday (though in theory you shouldn't have to!).
-
If StartDate = EndDate Then
-
WorkingDays = 1 'Change me to your needs.
-
Exit Function
-
End If
-
-
intCount = 1 ' Now we start counting days. 'If you always want to count the first day, set this to 1.
-
-
Do Until StartDate = EndDate
-
-
'First, we find out if this day is a weekday or a weekend.
-
'If weekday, 1 gets added to the number of days.
-
Select Case Weekday(StartDate)
-
Case Is = 1, 7
-
intCount = intCount 'Weekend, so nothing added.
-
Case Else
-
intCount = intCount + 1
-
End Select
-
-
'Now, if this day was a holiday, we take it back off again!
-
strWhere = "Holidays=#" & StartDate & "#" 'change fieldname.
-
If DCount("Holidays", "tbl_holidays", strWhere) > 0 Then 'Change to your field/table names.
-
intCount = intCount - 1
-
End If
-
-
StartDate = StartDate + 1 'We move to the next day.
-
-
Loop
-
-
WorkingDays = intCount
-
-
Exit_WorkingDays:
-
Exit Function
-
-
Err_WorkingDays:
-
MsgBox Err.Description
-
Resume Exit_WorkingDays
-
-
End Function
-
Gaz :)
i am about to try to use this right now.
Thank you!
@GazMathias
Follow up question,
Can the calculation be altered to process if either date is in a different format? like2/18/2008" and the other is 12/1/2008 3:55:53 PM"?
how might i have the program skip a day if the start date (submitted date) was after 4:59 pm?
i can only get this to work some of the time. usually all i get is overflow errors,
i have no idea what i am doing wrong
[quote] -
strWhere = "Holidays=#" & StartDate & "#" 'change fieldname.
-
If DCount("Holidays", "tbl_holidays", strWhere) > 0 Then 'Change to your field/table names.
-
intCount = intCount - 1
-
End If
-
:D
From some unknown to me reasons this code is everywhere in the net.
I just wonder who was the first to suggest this code.
Wouldn't it be more effective to run DCount once using range from StartDate till EndDate as criteria and holiday table filtered from weekends with a simple query as domain instead of running it on each day in the range?
Kind regards,
Fish.
@trixxnixon
Just add 7*60+1 minutes to the start date.
DateAdd() function is just for that.
Regards,
Fish
@trixxnixon - Create a Table named tblHolidays, with a single Field named DATE (DATE/TIME). Populate this Table with all the Holidays that you wish to exclude, namely 12/25/2008, 1/1/2009, etc.
- Copy and Paste the following code into a Standard Code Module:
- Public Function fExcludeHolidaysAndWeekEnds(dteStartDate As Date, dteEndDate As Date) As Integer
-
Dim intDayDiff As Integer
-
Dim intDayCounter As Integer
-
Dim dteCurrentDate As Date
-
Dim intWeekEndHolDates As Integer
-
-
intDayDiff = DateDiff("d", dteStartDate, dteEndDate)
-
-
For intDayCounter = 0 To intDayDiff
-
dteCurrentDate = DateAdd("d", intDayCounter, dteStartDate)
-
If Weekday(dteCurrentDate) = 7 Or Weekday(dteCurrentDate) = 1 Or DLookup(dteCurrentDate, "tblHolidays", "[Date] = #" & _
-
dteCurrentDate & "#") > 0 Then
-
intWeekEndHolDates = intWeekEndHolDates + 1
-
End If
-
Next
-
-
fExcludeHolidaysAndWeekEnds = intDayDiff - intWeekEndHolDates
-
End Function
- Call the Function ans pass to it the Dates in question.
- Dim intDaysDiff As Integer
-
-
intDaysDiff = fExcludeHolidaysAndWeekEnds(#12/1/2008#, #12/31/2008#)
-
MsgBox intDaysDiff
- The Return Value will be the number of Days between the 2 Dates, minus Weekends and Holidays.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ralph Freshour |
last post by:
What's a good way to calculate the number of days between two dates in
the following format:
2003-07-15
2003-08-02
I've looked at the PHP date functions but I'm still a bit lost...
|
by: pauly |
last post by:
Hello All,
I have been trying to create a query that extracts data from a table
and calculates the elapsed time between records. The table called
"Imported_table". I need to be able to calculate...
|
by: Rustan |
last post by:
Hi
Im using GregorianCalendar to find out the current years week numbers.
When the user chooses a week number in a dropdown i want to show that week
in a table with the corresponding dates.
For...
|
by: Paul Aspinall |
last post by:
Hi
I want to calculate the difference between 2 dates in C#.
I know there is a function in VB, called DateDiff, but I don't want to ref
the VB library, and want to try to do it natively in C#.
...
|
by: b.beeching |
last post by:
Not sure if my subject is entirely accurate but here goes.
I need to calculate the date difference between a date A and a date
B... however date B relies entily on date A.
EG: i have an advert...
|
by: rodneyeid |
last post by:
Hi,
I have an attendance machine which saves records in an Access Database in the following format : UserID DATE/TIME Checktype
where if checktype is 0 then its check in and if it is 1 then it...
|
by: rahulae |
last post by:
help me with this
I'm able to calculate total working days excluding weekends but how to exclude holidays,is there any other way apart from storing all the holidays in some table and not selecting...
|
by: aneinander |
last post by:
I'm beginner and getting following error message, please help.
The table contains transaction date, account number, branch, etc. And need to show previous transaction date or next transaction date...
|
by: nilanjangm |
last post by:
I am using Access 2007. In a table, I have two columns say Start_Date and End_Date of datatype Date/Time (Short Date).
Now, I want to calculate the duration of work in that period assuming 8 hours...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |