diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Expression.cs index 485e1b0..9df0147 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Expression.cs @@ -490,8 +490,7 @@ } public IEnumerator GetEnumerator() { - var ie = _value as IEnumerable; - return ie != null ? ie.GetEnumerator() : null; + return (_value as IEnumerable).GetEnumerator(); } } /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs index d454cfe..d1fe392 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/DateAndTime.cs @@ -1,5 +1,7 @@ using System; +using System.Collections; using System.Collections.Generic; +using System.Globalization; using System.Linq; using System.Text; @@ -12,108 +14,307 @@ ce.RegisterFunction("DATE", 3, Date); // Returns the serial number of a particular date ce.RegisterFunction("DATEVALUE", 1, Datevalue); // Converts a date in the form of text to a serial number ce.RegisterFunction("DAY", 1, Day); // Converts a serial number to a day of the month - //ce.RegisterFunction("DAYS360", 1, Days360); // Calculates the number of days between two dates based on a 360-day year - //ce.RegisterFunction("EDATE", 1, Edate); // Returns the serial number of the date that is the indicated number of months before or after the start date - //ce.RegisterFunction("EOMONTH", 1, Eomonth); // Returns the serial number of the last day of the month before or after a specified number of months + ce.RegisterFunction("DAYS360", 2, 3, Days360); // Calculates the number of days between two dates based on a 360-day year + ce.RegisterFunction("EDATE", 2, Edate); // Returns the serial number of the date that is the indicated number of months before or after the start date + ce.RegisterFunction("EOMONTH", 2, Eomonth); // Returns the serial number of the last day of the month before or after a specified number of months ce.RegisterFunction("HOUR", 1, Hour); // Converts a serial number to an hour ce.RegisterFunction("MINUTE", 1, Minute); // Converts a serial number to a minute ce.RegisterFunction("MONTH", 1, Month); // Converts a serial number to a month - //ce.RegisterFunction("NETWORKDAYS", 1, Networkdays); // Returns the number of whole workdays between two dates + ce.RegisterFunction("NETWORKDAYS", 2, 3, Networkdays); // Returns the number of whole workdays between two dates ce.RegisterFunction("NOW", 0, Now); // Returns the serial number of the current date and time ce.RegisterFunction("SECOND", 1, Second); // Converts a serial number to a second ce.RegisterFunction("TIME", 3, Time); // Returns the serial number of a particular time ce.RegisterFunction("TIMEVALUE", 1, Timevalue); // Converts a time in the form of text to a serial number ce.RegisterFunction("TODAY", 0, Today); // Returns the serial number of today's date - //ce.RegisterFunction("WEEKDAY", 1, Weekday); // Converts a serial number to a day of the week - //ce.RegisterFunction("WEEKNUM", 1, Weeknum); // Converts a serial number to a number representing where the week falls numerically with a year - //ce.RegisterFunction("WORKDAY", 1, Workday); // Returns the serial number of the date before or after a specified number of workdays + ce.RegisterFunction("WEEKDAY", 1, 2, Weekday); // Converts a serial number to a day of the week + ce.RegisterFunction("WEEKNUM", 1, 2, Weeknum); // Converts a serial number to a number representing where the week falls numerically with a year + ce.RegisterFunction("WORKDAY", 2, 3, Workday); // Returns the serial number of the date before or after a specified number of workdays ce.RegisterFunction("YEAR", 1, Year); // Converts a serial number to a year - //ce.RegisterFunction("YEARFRAC", 1, Yearfrac); // Returns the year fraction representing the number of whole days between start_date and end_date + ce.RegisterFunction("YEARFRAC", 2, 3, Yearfrac); // Returns the year fraction representing the number of whole days between start_date and end_date } - static object Date(List p) + private static object Date(List p) { - var year = (int)p[0]; - var month = (int)p[1]; - var day = (int)p[2]; + var year = (int) p[0]; + var month = (int) p[1]; + var day = (int) p[2]; - return (int)Math.Floor(new DateTime(year, month, day).ToOADate()); + return (int) Math.Floor(new DateTime(year, month, day).ToOADate()); } - static object Datevalue(List p) + private static object Datevalue(List p) { - var date = (string)p[0]; + var date = (string) p[0]; - return (int)Math.Floor(DateTime.Parse(date).ToOADate()); + return (int) Math.Floor(DateTime.Parse(date).ToOADate()); } - static object Day(List p) + private static object Day(List p) { - var date = (DateTime)p[0]; + var date = (DateTime) p[0]; return date.Day; } - static object Month(List p) + private static object Month(List p) { - var date = (DateTime)p[0]; + var date = (DateTime) p[0]; return date.Month; } - static object Year(List p) + private static object Year(List p) { - var date = (DateTime)p[0]; + var date = (DateTime) p[0]; return date.Year; } - static object Minute(List p) + private static object Minute(List p) { - var date = (DateTime)p[0]; + var date = (DateTime) p[0]; return date.Minute; } - static object Hour(List p) + private static object Hour(List p) { - var date = (DateTime)p[0]; + var date = (DateTime) p[0]; return date.Hour; } - static object Second(List p) + private static object Second(List p) { - var date = (DateTime)p[0]; + var date = (DateTime) p[0]; return date.Second; } - static object Now(List p) + private static object Now(List p) { return DateTime.Now; } - static object Time(List p) + private static object Time(List p) { - var hour = (int)p[0]; - var minute = (int)p[1]; - var second = (int)p[2]; - + var hour = (int) p[0]; + var minute = (int) p[1]; + var second = (int) p[2]; + return new TimeSpan(0, hour, minute, second); } - static object Timevalue(List p) + private static object Timevalue(List p) { - var date = (DateTime)p[0]; + var date = (DateTime) p[0]; return (DateTime.MinValue + date.TimeOfDay).ToOADate(); } - static object Today(List p) + private static object Today(List p) { return DateTime.Now.Date; } + + private static object Days360(List p) + { + var date1 = (DateTime) p[0]; + var date2 = (DateTime) p[1]; + var isEuropean = p.Count == 3 ? p[2] : false; + + return Days360(date1, date2, isEuropean); + } + + private static Int32 Days360(DateTime date1, DateTime date2, Boolean isEuropean) + { + var d1 = date1.Day; + var m1 = date1.Month; + var y1 = date1.Year; + var d2 = date2.Day; + var m2 = date2.Month; + var y2 = date2.Year; + + if (isEuropean) + { + if (d1 == 31) d1 = 30; + if (d2 == 31) d2 = 30; + } + else + { + if (d1 == 31) d1 = 30; + if (d2 == 31 && d1 == 30) d2 = 30; + } + + return 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1); + + } + + private static object Edate(List p) + { + var date = (DateTime)p[0]; + var mod = (int)p[1]; + + var retDate = date.AddMonths(mod); + return retDate; + } + + private static object Eomonth(List p) + { + var date = (DateTime)p[0]; + var mod = (int)p[1]; + + var retDate = date.AddMonths(mod); + return new DateTime(retDate.Year, retDate.Month, 1).AddMonths(1).AddDays(-1); + } + + private static object Networkdays(List p) + { + var date1 = (DateTime)p[0]; + var date2 = (DateTime)p[1]; + var bankHolidays = new List(); + if (p.Count == 3) + { + var t = new Tally {p[2]}; + + bankHolidays.AddRange(t.Select(XLHelper.GetDate)); + } + + return BusinessDaysUntil(date1, date2, bankHolidays); + } + + /// + /// Calculates number of business days, taking into account: + /// - weekends (Saturdays and Sundays) + /// - bank holidays in the middle of the week + /// + /// First day in the time interval + /// Last day in the time interval + /// List of bank holidays excluding weekends + /// Number of business days during the 'span' + private static int BusinessDaysUntil(DateTime firstDay, DateTime lastDay, IEnumerable bankHolidays) + { + firstDay = firstDay.Date; + lastDay = lastDay.Date; + if (firstDay > lastDay) + throw new ArgumentException("Incorrect last day " + lastDay); + + TimeSpan span = lastDay - firstDay; + int businessDays = span.Days + 1; + int fullWeekCount = businessDays / 7; + // find out if there are weekends during the time exceedng the full weeks + if (businessDays > fullWeekCount * 7) + { + // we are here to find out if there is a 1-day or 2-days weekend + // in the time interval remaining after subtracting the complete weeks + var firstDayOfWeek = (int)firstDay.DayOfWeek; + var lastDayOfWeek = (int)lastDay.DayOfWeek; + if (lastDayOfWeek < firstDayOfWeek) + lastDayOfWeek += 7; + if (firstDayOfWeek <= 6) + { + if (lastDayOfWeek >= 7)// Both Saturday and Sunday are in the remaining time interval + businessDays -= 2; + else if (lastDayOfWeek >= 6)// Only Saturday is in the remaining time interval + businessDays -= 1; + } + else if (firstDayOfWeek <= 7 && lastDayOfWeek >= 7)// Only Sunday is in the remaining time interval + businessDays -= 1; + } + + // subtract the weekends during the full weeks in the interval + businessDays -= fullWeekCount + fullWeekCount; + + // subtract the number of bank holidays during the time interval + foreach (var bh in bankHolidays) + { + if (firstDay <= bh && bh <= lastDay) + --businessDays; + } + + return businessDays; + } + + private static object Weekday(List p) + { + var dayOfWeek = (int)((DateTime)p[0]).DayOfWeek; + var retType = p.Count == 2 ? (int)p[1] : 1; + + if (retType == 2) return dayOfWeek; + if (retType == 1) return dayOfWeek + 1; + + return dayOfWeek - 1; + } + + private static object Weeknum(List p) + { + var date = (DateTime)p[0]; + var retType = p.Count == 2 ? (int)p[1] : 1; + + DayOfWeek dayOfWeek = retType == 1 ? DayOfWeek.Sunday : DayOfWeek.Monday; + var cal = new GregorianCalendar(GregorianCalendarTypes.Localized); + var val = cal.GetWeekOfYear(date, CalendarWeekRule.FirstDay, dayOfWeek); + + return val; + } + + private static object Workday(List p) + { + var startDate = (DateTime)p[0]; + 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) + { + var t = new Tally { p[2] }; + + bankHolidays.AddRange(t.Select(XLHelper.GetDate)); + } + var testDate = startDate.AddDays(((daysRequired / 7) + 2) * 7); + return Workday(startDate, testDate, daysRequired, bankHolidays).NextWorkday(bankHolidays); + } + + private static DateTime Workday(DateTime startDate, DateTime testDate, int daysRequired, IEnumerable bankHolidays) + { + + var businessDays = BusinessDaysUntil(startDate, testDate, bankHolidays); + if (businessDays == daysRequired) + return testDate; + + int days = businessDays > daysRequired ? -1 : 1; + + return Workday(startDate, testDate.AddDays(days), daysRequired, bankHolidays); + } + + private static object Yearfrac(List p) + { + var date1 = (DateTime) p[0]; + var date2 = (DateTime) p[1]; + var option = p.Count == 3 ? (int)p[2] : 0; + + if (option == 0) + return Days360(date1, date2, false) / 360.0; + if (option == 1) + return Math.Floor((date2 - date1).TotalDays) / GetYearAverage(date1, date2); + if (option == 2) + return Math.Floor((date2 - date1).TotalDays) / 360.0; + if (option == 3) + return Math.Floor((date2 - date1).TotalDays) / 365.0; + + return Days360(date1, date2, true) / 360.0; + } + + private static Double GetYearAverage(DateTime date1, DateTime date2) + { + var daysInYears = new List(); + for (int year = date1.Year; year <= date2.Year; year++) + daysInYears.Add(DateTime.IsLeapYear(year) ? 366 : 365); + return daysInYears.Average(); + } } -} + +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/Tally.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/Tally.cs index 6b5cf45..bbf5f63 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/Tally.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CalcEngine/Functions/Tally.cs @@ -6,7 +6,7 @@ namespace ClosedXML.Excel.CalcEngine { - internal class Tally + internal class Tally: IEnumerable { private readonly List _list = new List(); @@ -35,7 +35,7 @@ // handle expressions var val = e.Evaluate(); var valEnumerable = val as IEnumerable; - if (valEnumerable == null) + if (valEnumerable == null || val is string) _list.Add(val); else foreach (var v in valEnumerable) @@ -170,5 +170,15 @@ return ret; } + + public IEnumerator GetEnumerator() + { + return _list.GetEnumerator(); + } + + IEnumerator IEnumerable.GetEnumerator() + { + return GetEnumerator(); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs index 44771ba..7c92884 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs @@ -147,6 +147,18 @@ return 2958465.99999999; } } + + public static DateTime NextWorkday(this DateTime date, List bankHolidays) + { + var nextDate = date.AddDays(1); + while (nextDate.DayOfWeek == DayOfWeek.Saturday + || nextDate.DayOfWeek == DayOfWeek.Sunday + || bankHolidays.Contains(nextDate) + ) + nextDate = nextDate.AddDays(1); + + return nextDate; + } } public static class IntegerExtensions diff --git a/ClosedXML/ClosedXML/ClosedXML/XLHelper.cs b/ClosedXML/ClosedXML/ClosedXML/XLHelper.cs index 769e532..1924814 100644 --- a/ClosedXML/ClosedXML/ClosedXML/XLHelper.cs +++ b/ClosedXML/ClosedXML/ClosedXML/XLHelper.cs @@ -287,5 +287,23 @@ { return Math.Abs(d1 - d2) < Epsilon; } + + public static DateTime GetDate(Object v) + { + // handle dates + if (v is DateTime) + { + return (DateTime)v; + } + + // handle doubles + if (v is double) + { + return DateTime.FromOADate((double)v); + } + + // handle everything else + return (DateTime)Convert.ChangeType(v, typeof(DateTime)); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs index a999c43..0c4645f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/CalcEngine/DateAndTimeTests.cs @@ -97,5 +97,287 @@ Object actual = XLWorkbook.EvaluateExpr("Today()"); Assert.AreEqual(DateTime.Now.Date, actual); } + + [TestMethod] + public void Days360_Default() + { + Object actual = XLWorkbook.EvaluateExpr("Days360(\"1/30/2008\", \"2/1/2008\")"); + Assert.AreEqual(1, actual); + } + + [TestMethod] + public void Days360_US1() + { + Object actual = XLWorkbook.EvaluateExpr("DAYS360(\"1/1/2008\", \"3/31/2008\",FALSE)"); + Assert.AreEqual(90, actual); + } + + [TestMethod] + public void Days360_US2() + { + Object actual = XLWorkbook.EvaluateExpr("DAYS360(\"3/31/2008\", \"1/1/2008\",FALSE)"); + Assert.AreEqual(-89, actual); + } + + [TestMethod] + public void Days360_Europe1() + { + Object actual = XLWorkbook.EvaluateExpr("DAYS360(\"1/1/2008\", \"3/31/2008\",TRUE)"); + Assert.AreEqual(89, actual); + } + + [TestMethod] + public void Days360_Europe2() + { + Object actual = XLWorkbook.EvaluateExpr("DAYS360(\"3/31/2008\", \"1/1/2008\",TRUE)"); + Assert.AreEqual(-89, actual); + } + + [TestMethod] + public void EDate_Positive1() + { + Object actual = XLWorkbook.EvaluateExpr("EDate(\"3/1/2008\", 1)"); + Assert.AreEqual(new DateTime(2008, 4, 1), actual); + } + + [TestMethod] + public void EDate_Positive2() + { + Object actual = XLWorkbook.EvaluateExpr("EDate(\"3/31/2008\", 1)"); + Assert.AreEqual(new DateTime(2008, 4, 30), actual); + } + + [TestMethod] + public void EDate_Negative1() + { + Object actual = XLWorkbook.EvaluateExpr("EDate(\"3/1/2008\", -1)"); + Assert.AreEqual(new DateTime(2008, 2, 1), actual); + } + + [TestMethod] + public void EDate_Negative2() + { + Object actual = XLWorkbook.EvaluateExpr("EDate(\"3/31/2008\", -1)"); + Assert.AreEqual(new DateTime(2008, 2, 29), actual); + } + + [TestMethod] + public void EOMonth_Positive() + { + Object actual = XLWorkbook.EvaluateExpr("EOMonth(\"3/31/2008\", 1)"); + Assert.AreEqual(new DateTime(2008, 4, 30), actual); + } + + [TestMethod] + public void EOMonth_Negative() + { + Object actual = XLWorkbook.EvaluateExpr("EOMonth(\"3/1/2008\", -1)"); + Assert.AreEqual(new DateTime(2008, 2, 29), actual); + } + + [TestMethod] + public void Networkdays_NoHolidaysGiven() + { + Object actual = XLWorkbook.EvaluateExpr("Networkdays(\"10/01/2008\", \"3/01/2009\")"); + Assert.AreEqual(108, actual); + } + + [TestMethod] + public void Networkdays_OneHolidaysGiven() + { + Object actual = XLWorkbook.EvaluateExpr("Networkdays(\"10/01/2008\", \"3/01/2009\", \"11/26/2008\")"); + Assert.AreEqual(107, actual); + } + + [TestMethod] + public void Networkdays_MultipleHolidaysGiven() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Date") + .CellBelow().SetValue(new DateTime(2008, 10, 1)) + .CellBelow().SetValue(new DateTime(2009, 3, 1)) + .CellBelow().SetValue(new DateTime(2008, 11, 26)) + .CellBelow().SetValue(new DateTime(2008, 12, 4)) + .CellBelow().SetValue(new DateTime(2009, 1, 21)); + Object actual = ws.Evaluate("Networkdays(A2,A3,A4:A6)"); + Assert.AreEqual(105, actual); + } + + [TestMethod] + public void Weekday_Omitted() + { + Object actual = XLWorkbook.EvaluateExpr("Weekday(\"2/14/2008\")"); + Assert.AreEqual(5, actual); + } + + [TestMethod] + public void Weekday_1() + { + Object actual = XLWorkbook.EvaluateExpr("Weekday(\"2/14/2008\", 1)"); + Assert.AreEqual(5, actual); + } + + [TestMethod] + public void Weekday_2() + { + Object actual = XLWorkbook.EvaluateExpr("Weekday(\"2/14/2008\", 2)"); + Assert.AreEqual(4, actual); + } + + [TestMethod] + public void Weekday_3() + { + Object actual = XLWorkbook.EvaluateExpr("Weekday(\"2/14/2008\", 3)"); + Assert.AreEqual(3, actual); + } + + [TestMethod] + public void Weeknum_Default() + { + Object actual = XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2008\")"); + Assert.AreEqual(11, actual); + } + + [TestMethod] + public void Weeknum_1() { Assert.AreEqual(11, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2000\", 1)")); } + [TestMethod] + public void Weeknum_2() { Assert.AreEqual(11, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2000\", 2)")); } + [TestMethod] + public void Weeknum_3() { Assert.AreEqual(10, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2001\", 1)")); } + [TestMethod] + public void Weeknum_4() { Assert.AreEqual(10, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2001\", 2)")); } + [TestMethod] + public void Weeknum_5() { Assert.AreEqual(10, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2002\", 1)")); } + [TestMethod] + public void Weeknum_6() { Assert.AreEqual(10, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2002\", 2)")); } + [TestMethod] + public void Weeknum_7() { Assert.AreEqual(11, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2003\", 1)")); } + [TestMethod] + public void Weeknum_8() { Assert.AreEqual(10, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2003\", 2)")); } + [TestMethod] + public void Weeknum_9() { Assert.AreEqual(11, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2004\", 1)")); } + [TestMethod] + public void Weeknum_10() { Assert.AreEqual(11, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2004\", 2)")); } + [TestMethod] + public void Weeknum_11() { Assert.AreEqual(11, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2005\", 1)")); } + [TestMethod] + public void Weeknum_12() { Assert.AreEqual(11, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2005\", 2)")); } + [TestMethod] + public void Weeknum_13() { Assert.AreEqual(10, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2006\", 1)")); } + [TestMethod] + public void Weeknum_14() { Assert.AreEqual(11, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2006\", 2)")); } + [TestMethod] + public void Weeknum_15() { Assert.AreEqual(10, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2007\", 1)")); } + [TestMethod] + public void Weeknum_16() { Assert.AreEqual(10, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2007\", 2)")); } + [TestMethod] + public void Weeknum_17() { Assert.AreEqual(11, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2008\", 1)")); } + [TestMethod] + public void Weeknum_18() { Assert.AreEqual(10, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2008\", 2)")); } + [TestMethod] + public void Weeknum_19() { Assert.AreEqual(11, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2009\", 1)")); } + [TestMethod] + public void Weeknum_20() { Assert.AreEqual(11, XLWorkbook.EvaluateExpr("Weeknum(\"3/9/2009\", 2)")); } + + + [TestMethod] + public void Workdays_NoHolidaysGiven() + { + Object actual = XLWorkbook.EvaluateExpr("Workday(\"10/01/2008\", 151)"); + Assert.AreEqual(new DateTime(2009, 4,30), actual); + } + + [TestMethod] + public void Workdays_OneHolidaysGiven() + { + Object actual = XLWorkbook.EvaluateExpr("Workday(\"10/01/2008\", 152, \"11/26/2008\")"); + Assert.AreEqual(new DateTime(2009, 5, 4), actual); + } + + [TestMethod] + public void Workdays_MultipleHolidaysGiven() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Date") + .CellBelow().SetValue(new DateTime(2008, 10, 1)) + .CellBelow().SetValue(151) + .CellBelow().SetValue(new DateTime(2008, 11, 26)) + .CellBelow().SetValue(new DateTime(2008, 12, 4)) + .CellBelow().SetValue(new DateTime(2009, 1, 21)); + Object actual = ws.Evaluate("Workday(A2,A3,A4:A6)"); + Assert.AreEqual(new DateTime(2009, 5, 5), actual); + } + + [TestMethod] + public void Yearfrac_1_base0() + { + Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2008\",0)"); + Assert.IsTrue(XLHelper.AreEqual(0.25, (double)actual)); + } + + [TestMethod] + public void Yearfrac_1_base1() + { + Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2008\",1)"); + Assert.IsTrue(XLHelper.AreEqual(0.24590163934426229, (double)actual)); + } + + [TestMethod] + public void Yearfrac_1_base2() + { + Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2008\",2)"); + Assert.IsTrue(XLHelper.AreEqual(0.25, (double)actual)); + } + + [TestMethod] + public void Yearfrac_1_base3() + { + Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2008\",3)"); + Assert.IsTrue(XLHelper.AreEqual(0.24657534246575341, (double)actual)); + } + + [TestMethod] + public void Yearfrac_1_base4() + { + Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2008\",4)"); + Assert.IsTrue(XLHelper.AreEqual(0.24722222222222223, (double)actual)); + } + + [TestMethod] + public void Yearfrac_2_base0() + { + Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2013\",0)"); + Assert.IsTrue(XLHelper.AreEqual(5.25, (double)actual)); + } + + [TestMethod] + public void Yearfrac_2_base1() + { + Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2013\",1)"); + Assert.IsTrue(XLHelper.AreEqual(5.24452554744526, (double)actual)); + } + + [TestMethod] + public void Yearfrac_2_base2() + { + Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2013\",2)"); + Assert.IsTrue(XLHelper.AreEqual(5.32222222222222, (double)actual)); + } + + [TestMethod] + public void Yearfrac_2_base3() + { + Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2013\",3)"); + Assert.IsTrue(XLHelper.AreEqual(5.24931506849315, (double)actual)); + } + + [TestMethod] + public void Yearfrac_2_base4() + { + Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2013\",4)"); + Assert.IsTrue(XLHelper.AreEqual(5.24722222222222, (double)actual)); + } } }