I am working on a scheduling project in .NET, it is something that could be see as similar to MS Project (scheduling task and calculating durations).
I am working through the date and time calculations and I was wondering how are all of you managing your Holidays and Non-Workday calculations?
Specifically in terms of functionality something that does the following:
Add a number of days to a start date.
Calculate the number of workdays between two dates.
Determine if a date is a workday or not.
There are a few options that I have thought of:
Store all working days in a Database and perform lookups or potentially offsets to perform date math. This could also be something like an in-memory hashset of all working days as well.
Create a rule based system (Christmas is the 25 day of the 12 month of every year)
I am interested to hear your thoughts.
Add holidays to your database, they are the exceptions so way less configuration. Then write some logic to take them into account when you do a date diff between two dates.
I’d say the easiest thing is to just have a datastore that contains the various holidays or other information you need. I think your idea of some kind of in-memory data store, as I imagine holidays would be a relatively small set of records.
The rule based approach could work but you’d still need to store your rule sets somewhere, otherwise I think that’d just get unmanageable real quick. I don’t see any advantage to this approach over just storing the holiday data directly.
Edit : Just wanted to mention something you may need to consider. What qualifies as a “working day” is going to differ by business/company/user, same goes for holidays. If multiple businesses are going to be using your app, you’re going to need to know this data for each. Also, as someone else mentioned already, you need to consider how to deal with differing time zones and hemispheres. How are you going to deal with varying time zones and dates?
What if someone in Saudi Arabia was to use your software?
What if someone in
Saudi Arabia was
To use your software?
– theasymptote
I detect haikus. And sometimes, successfully. Learn more about me.
Opt out of replies: “haikusbot opt out” | Delete my comment: “haikusbot delete”
Thank you everyone for your input! I am leaning towards a database that contains all of the holidays for a given calendar as you have all mentioned.
For the actual logic to calculate a work date offset I was thinking of the following:
Get a collection of holidays for a given calendar from a data source. Then, generate an in-memory dual-dictionary containing the workdays using the holiday collection and weekends.
This dual dictionary could look something like this:
Then I can use simple addition and subtraction to find the end date from the start date:
Let me know your thougts.
That could work as long as you know holiday and work week data per user/business that is using your app. If multiple businesses are going to use your application, then a global standard isn’t going to be accurate. If you collect data on what the business considers working days as well as any holidays/days of interest they recognize, then you can make accurate calculations.
Kind of a brain dump, but here are some things I’d want to keep in mind if I were working on this project:
Different countries celebrate different holidays
Even countries that celebrate a certain holiday might not require time off on that day (which I assume is the key detail you’re trying to encode into your program)
Even if a company declares a day a holiday, not all employees are going to be off that day (on-call, essential workers like grocery or EMS). This may/may not matter for your goals.
Not all countries celebrate a certain holiday on the same day (e.g. Canadian Thanksgiving). This may be easily fixed by considering them separate holidays.
A single company may not apply the same holiday schedule to all of its employees (e.g. their employees in India get Indian holidays off, U.S. employees get U.S. holidays off)
Holidays are not always on the same date in each year (U.S. Thanksgiving is the 4th Thursday each November)
If your project does tracking down to the hour, consider that time zones mean a single holiday is not necessarily celebrated in the same 24-hour bucket (New Years Day in Australia can be >12 hours difference from New Years in U.S.)
A single holiday may span multiple days (Chinese New Year in China often lasts up to 1 week)
A single holiday does not necessarily follow the same “rule pattern” every year, in perpetuity (U.S. Memorial day was celebrated May 30 until 1970, then moved to the last Monday of May)
Companies do not necessarily “celebrate” a holiday on its federally-designated day (if Christmas falls on a Saturday, many companies give one day off in either the preceding or following week)
Given all of that, what I’d probably do is create a database table that simply holds a list of holidays, any appropriate Country/Company/Regional filters (to make sure it’s possible to pull “all holidays that apply to Employee X”), and a start/end date for the holiday. This table has one row for each holiday for each year. you can use a .NET rule-based program or something to proactively fill the Holiday table as many years in advance as you need to plan for (but make sure more can be added later – some companies will release guidance in Q4 of one year designating a new holiday for the next fiscal year, so you’ve got to be able to support that).
Combine that holidays table with a Calendar Table (it can be pretty simple, just need the date really). If you want to know how many workdays exist between point A and B, just:
Join your Holidays table to the Calendar table to get a listing of all days covered. For example, your Holiday table may contain Start/End date but after joining you’ll get a list of all consecutive days between the start and end.
Select all days from Calendar table except those in the “consecutive list of holidays” found in the previous step.
Further filter out days that don’t match your desired date range.
Finally filter out weekends.
Count the number of days remaining.
Here’s an example:
http://sqlfiddle.com/#!18/09204/16
Schema:
Count Query:
C# devs
null reference exceptions