diff --git a/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs b/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs index d1fe392..3fa8e30 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs @@ -198,7 +198,7 @@ firstDay = firstDay.Date; lastDay = lastDay.Date; if (firstDay > lastDay) - throw new ArgumentException("Incorrect last day " + lastDay); + return -BusinessDaysUntil(lastDay, firstDay, bankHolidays); TimeSpan span = lastDay - firstDay; int businessDays = span.Days + 1; @@ -265,7 +265,6 @@ var daysRequired = (int)p[1]; if (daysRequired == 0) return startDate; - if (daysRequired < 0) throw new ArgumentOutOfRangeException("DaysRequired must be >= 0."); var bankHolidays = new List(); if (p.Count == 3) @@ -274,13 +273,18 @@ bankHolidays.AddRange(t.Select(XLHelper.GetDate)); } - var testDate = startDate.AddDays(((daysRequired / 7) + 2) * 7); - return Workday(startDate, testDate, daysRequired, bankHolidays).NextWorkday(bankHolidays); + var testDate = startDate.AddDays(((daysRequired / 7) + 2) * 7 * Math.Sign(daysRequired)); + var return_date = Workday(startDate, testDate, daysRequired, bankHolidays); + if (Math.Sign(daysRequired) == 1) + return_date = return_date.NextWorkday(bankHolidays); + else + return_date = return_date.PreviousWorkDay(bankHolidays); + + return return_date; } private static DateTime Workday(DateTime startDate, DateTime testDate, int daysRequired, IEnumerable bankHolidays) { - var businessDays = BusinessDaysUntil(startDate, testDate, bankHolidays); if (businessDays == daysRequired) return testDate; diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index 1a33550..8845037 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -149,17 +149,31 @@ } } - public static DateTime NextWorkday(this DateTime date, List bankHolidays) + public static DateTime NextWorkday(this DateTime date, IEnumerable bankHolidays) { var nextDate = date.AddDays(1); - while (nextDate.DayOfWeek == DayOfWeek.Saturday - || nextDate.DayOfWeek == DayOfWeek.Sunday - || bankHolidays.Contains(nextDate) - ) + while (!nextDate.IsWorkDay(bankHolidays)) nextDate = nextDate.AddDays(1); return nextDate; } + + public static DateTime PreviousWorkDay(this DateTime date, IEnumerable bankHolidays) + { + var previousDate = date.AddDays(-1); + while (!previousDate.IsWorkDay(bankHolidays)) + previousDate = previousDate.AddDays(-1); + + return previousDate; + } + + public static bool IsWorkDay(this DateTime date, IEnumerable bankHolidays) + { + return date.DayOfWeek != DayOfWeek.Saturday + && date.DayOfWeek != DayOfWeek.Sunday + && !bankHolidays.Contains(date); + } + } public static class IntegerExtensions diff --git a/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs b/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs index 6213f8c..05421cd 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs @@ -170,6 +170,16 @@ } [Test] + public void Networkdays_NegativeResult() + { + Object actual = XLWorkbook.EvaluateExpr("Networkdays(\"3/01/2009\", \"10/01/2008\")"); + Assert.AreEqual(-108, actual); + + actual = XLWorkbook.EvaluateExpr("Networkdays(\"2016-01-01\", \"2015-12-23\")"); + Assert.AreEqual(-8, actual); + } + + [Test] public void Networkdays_OneHolidaysGiven() { Object actual = XLWorkbook.EvaluateExpr("Networkdays(\"10/01/2008\", \"3/01/2009\", \"11/26/2008\")"); @@ -387,6 +397,9 @@ { Object actual = XLWorkbook.EvaluateExpr("Workday(\"10/01/2008\", 151)"); Assert.AreEqual(new DateTime(2009, 4, 30), actual); + + actual = XLWorkbook.EvaluateExpr("Workday(\"2016-01-01\", -10)"); + Assert.AreEqual(new DateTime(2015, 12, 18), actual); } [Test]