Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the next working day, excluding weekends and holidays

I have a requirement where I need to work on a date field, so the requirement is some thing like this

I will call the field as minimum possible date

  1. Add +1 to the date

  2. If the minimum possible date happens to fall on a weekend(Sat or Sun) after adding 1 day, Display the next working day i.e Monday

  3. If the minimum possible date happens to fall on a Holiday, display the next working day. (Holidays 1.1 , 1.5 , 3.10 , 25.12 , 26.12)

  4. If the minimum possible date happens to fall on a weekend(Sat or Sun) after adding 1 day, and the day after that is a holiday then show the next working day. Eg: After +1 day if min possible day is Saturday, we will have to display Monday. But if Monday happens to be a Holiday then we have to display Tuesday.

I have tried a solution to the above problem by having multiple if and else cases, but just wondering if there is any generic and graceful way of doing it?

I have tried

var holidays = new List<DateTime>();
holidays.Add(new DateTime(DateTime.Now.Year,1,1));
holidays.Add(new DateTime(DateTime.Now.Year,1,5));
holidays.Add(new DateTime(DateTime.Now.Year,3,10));
holidays.Add(new DateTime(DateTime.Now.Year,12,25));

if (date.DayOfWeek == DayOfWeek.Saturday || date.DayOfWeek == DayOfWeek.Sunday)
{
     //Logic to add +1 and again some logic to check for weekends and weekdays
}
else if (holidays.Contain(date))
{
   //Logic to add +1 and again some logic to check for weekends and weekdays
}
like image 455
CSharped Avatar asked Oct 27 '15 15:10

CSharped


People also ask

How do you add working days in Excel excluding weekends and holidays?

To add days excluding weekends, you can do as below: Select a blank cell and type this formula =WORKDAY(A2,B2), and press Enter key to get result. Tip: In the formula, A2 is the start date, B2 is the days you want to add. Now the end date which add 45 business days excluding weekends has been shown.

How do you calculate your next working day?

To get the next working day, or next business day, you can use the WORKDAY function. The WORKDAY formula is fully automatic. Given a date and days, it will add days to the date, taking into account weekends and, optionally, holidays.

How do you calculate working days in Excel excluding weekends and holidays in SQL?

You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any. For example check below query. And If You want to exclude holiday's too, then, You also can calculate holidays between start/end date and can subtract that from final selection.


3 Answers

Basically you want to get the next working day. So you could loop on this condition adding 1 day to the current date

do {   date = date.AddDays(1); } while(IsHoliday(date) || IsWeekend(date)); 

In the previous code IsHoliday is a predicate telling if a date is holiday. For instance, shamelessly reusing your code:

class Program {     private static readonly HashSet<DateTime> Holidays = new HashSet<DateTime>();      private static bool IsHoliday(DateTime date)     {         return Holidays.Contains(date);     }      private static bool IsWeekend(DateTime date)     {         return date.DayOfWeek == DayOfWeek.Saturday             || date.DayOfWeek == DayOfWeek.Sunday;     }       private static DateTime GetNextWorkingDay(DateTime date)     {         do         {             date = date.AddDays(1);         } while (IsHoliday(date) || IsWeekend(date));         return date;     }      static void Main(string[] args)     {         Holidays.Add(new DateTime(DateTime.Now.Year, 1, 1));         Holidays.Add(new DateTime(DateTime.Now.Year, 1, 5));         Holidays.Add(new DateTime(DateTime.Now.Year, 3, 10));         Holidays.Add(new DateTime(DateTime.Now.Year, 12, 25));          var dt = GetNextWorkingDay(DateTime.Parse(@"2015-10-31"));          Console.WriteLine(dt);          Console.ReadKey();     } } 
like image 118
fjardon Avatar answered Sep 25 '22 18:09

fjardon


Based on the answer from @fjardon, you can use my project Nager.Date It contains the public holidays and weekends for more than 100 countries.

nuget

PM> install-package Nager.Date

Code snippet

//usings
using Nager.Date;
using Nager.Date.Extensions;

//logic
var date = DateTime.Today; //Set start date
var countryCode = CountryCode.US; //Set country

do
{
    date = date.AddDays(1);
} while (DateSystem.IsPublicHoliday(date, countryCode) || date.IsWeekend(countryCode));
like image 20
Tino Hager Avatar answered Sep 23 '22 18:09

Tino Hager


A fixed list of dates is a somewhat limited way to express holidays.

Consider that your list only contains dates for the current year, so if today is December 30th 2015, then the next holiday would be Jan 1, 2016 - which you wouldn't find in your list.

Also consider that many holidays do not fall on the same date every year. Often they are tied to the day of the week, and sometimes they are determined by religious calendars, or arbitrarily.

A more robust system should handle a variety of different types of holidays. Here's one possible implementation:

public abstract class Holiday
{
    public abstract DateTime? GetDate(int year);
}

public class MonthDayBasedHoliday : Holiday
{
    private readonly int _month;
    private readonly int _day;

    public MonthDayBasedHoliday(int month, int day)
    {
        _month = month;
        _day = day;
    }

    public override DateTime? GetDate(int year)
    {
        return new DateTime(year, _month, _day);
    }
}

public class DayOfWeekBasedHoliday : Holiday
{
    private readonly int _occurrence;
    private readonly DayOfWeek _dayOfWeek;
    private readonly int _month;

    public DayOfWeekBasedHoliday(int occurrence, DayOfWeek dayOfWeek, int month)
    {
        _occurrence = occurrence;
        _dayOfWeek = dayOfWeek;
        _month = month;
    }

    public override DateTime? GetDate(int year)
    {
        if (_occurrence <= 4)
        {
            DateTime dt = new DateTime(year, _month, 1);
            int delta = (_dayOfWeek - dt.DayOfWeek + 7) % 7;
            delta += 7 * (_occurrence - 1);
            return dt.AddDays(delta);
        }
        else  // last occurrence in month
        {
            int daysInMonth = DateTime.DaysInMonth(year, _month);
            DateTime dt = new DateTime(year, _month, daysInMonth);
            int delta = (dt.DayOfWeek - _dayOfWeek + 7) % 7;
            return dt.AddDays(-delta);
        }
    }
}

public class FixedDateBasedHoliday : Holiday
{
    private readonly IDictionary<int, DateTime> _dates;

    public FixedDateBasedHoliday(params DateTime[] dates)
    {
        _dates = dates.ToDictionary(x => x.Year, x => x);
    }

    public override DateTime? GetDate(int year)
    {
        if (_dates.ContainsKey(year))
            return _dates[year];

        // fixed date not established for year
        return null;
    }
}

With these defined, we can now define holidays much more robustly, such as:

var holidays = new List<Holiday>();

// New Year's Day
holidays.Add(new MonthDayBasedHoliday(1, 1));

// President's Day (US)
holidays.Add(new DayOfWeekBasedHoliday(3, DayOfWeek.Monday, 2));

// Easter (Western Observance)
holidays.Add(new FixedDateBasedHoliday(new DateTime(2015, 4, 5), new DateTime(2016, 3, 27)));

// Memorial Day (US)
holidays.Add(new DayOfWeekBasedHoliday(5, DayOfWeek.Monday, 5));

// Christmas Day
holidays.Add(new MonthDayBasedHoliday(12, 25));

And now, we can create a method that checks for the next working day, as you requested:

public static DateTime GetNextNonHolidayWeekDay(DateTime date, IList<Holiday> holidays, IList<DayOfWeek> weekendDays)
{
    // always start with tomorrow, and truncate time to be safe
    date = date.Date.AddDays(1);

    // calculate holidays for both this year and next year
    var holidayDates = holidays.Select(x => x.GetDate(date.Year))
        .Union(holidays.Select(x => x.GetDate(date.Year + 1)))
        .Where(x=> x != null)
        .Select(x=> x.Value)
        .OrderBy(x => x).ToArray();

    // increment until we get a non-weekend and non-holiday date
    while (true)
    {
        if (weekendDays.Contains(date.DayOfWeek) || holidayDates.Contains(date))
            date = date.AddDays(1);
        else
            return date;
    }
}

That method could go on the abstract Holiday class, or it could go anywhere really.

Example usage (with above definition for holidays):

var weekendDays = new[] { DayOfWeek.Saturday, DayOfWeek.Sunday };

DateTime workDay = GetNextNonHolidayWeekDay(new DateTime(2015, 12, 31), holidays, weekendDays);
// returns 2016-01-04

This is still not quite a complete solution though. Many holidays have more complex calculation rules. As an exercise left to the reader, try implementing a class that derives from Holiday for the second day in the US Thanksgiving holiday. The first day will always fall on the 4th Thursday in November, but the second day is always "the Friday following the 4th Thursday in November", rather than just "the 4th Friday in November" (see November 2019 for an example of where this matters).

like image 25
Matt Johnson-Pint Avatar answered Sep 25 '22 18:09

Matt Johnson-Pint