MoonPoint Support Logo


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals

Advanced Search
Sun Mon Tue Wed Thu Fri Sat

Fri, Mar 04, 2016 9:37 pm

Workday function

Sometimes you may need to determine the number of work days from one date to another in a Microsoft Excel, Google Sheets, or Apache OpenOffice Calc spreadsheet. E.g., you may need to exclude Saturdays, Sundays, and holidays from a calculation. All three spreadsheet programs provide a WORKDAY function that you can use to calculate dates based on business work days rather than just the total number of days from a start date. For Microsoft Excel on both Microsoft Windows and OS X and Google Sheets, the syntax for the WORKDAY function is as follows:

WORKDAY(start_date, num_days, [holidays])

For OpenOffice Calc, you need to separate the parameters with a semicolon rather than a colon. I.e.:

WORKDAY(Start_date; num_days; [holidays])

If you separate the parameters with a comma in Calc, it will display Err:508.

"Holidays" is enclosed in brackets to indicate it is an optional parameter, you would not actually include the brackets if you specified holidays.

The arguments to the function have the following meaning:

E.g., I need to determine the date when work requests that should be completed within 5 business days after approval should be completed. I can display the required implementation date for each request in a worksheet by using the formula =WORKDAY(cell,5) where cell holds the approval date. E.g., if the approval date is in cell A2, I can set the formula for the required implemenation date to be WORKDAY(A2,5) for Excel or Google Sheets or WORKDAY(A2;5) for Apache OpenOffice Calc.

If there was no date in cell A2, Excel will display January 6, 1900 in the cell holding the implemenation date, e.g. 1/6/00, if the date format is D/M/YY (day/month/2-digit year), but OpenOffice Calc and Google Sheets will display January 5, 1900. Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations and, by default, uses January 1, 1900 as serial number 1. If you have a date field containing January 1, 2008 that is equivalent to 39,448, which you would see if you put that day in a cell formatted to hold a date then changed the format to be a number. January 1, 2016 is 42,370. January 1, 2008 is 39,448 days after January 1, 1900 and January 1, 2016 is 42,370 days after that date.

Why do Google Sheets and Apache OpenOffice Calc show January 5, 1900 as the calculated date when there is no date in the start_date field while Microsot Excel displays January 6, 1900? Long before Microsoft became a dominant player in the spreadsheet market, Lotus 1-2-3 from Lotus Development Corporation was a dominant spreadsheet in the personal computer market. There was a bug in Lotus 1-2-3 due to the developers assuming that the year 1900 was a leap year; it was not. When Microsoft released Excel they had to compete with Lotus 1-2-3, the then dominant spreadsheet for personal computers, so Microsoft needed to ensure that spreadsheets created in Lotus 1-2-3 returned the same results in Excel, so for compatibility retained the incorrect assumption regarding the year 1900 - see the Microsoft article Excel incorrectly assumes that the year 1900 is a leap year and Excel Date Conversion (Days from 1900) regarding making an allowance for that incorrect assumption. Google Sheets and Apache OpenOffice Calc did not retain that incorrect assumption regarding the year 1900 and so you will see January 5 used in certain calculations in those spreadsheets where January 6, 1900 is used by Exel, which is why sometims you may get differing results for date calculations between those spreadsheet programs.

If an argument to the workday function is not a valid date, e.g., 2/30/16 for February 30, 2016, which is not a valid date, you will see #VALUE! displayed by the function in all three spreadsheets. In all three spreadsheets, if days is not an integer, the number is truncated to an integer value. E.g., if you used the formula =WORKDAY(A2,5.7) you would get the same result as entering WORKDAY(A2,5).

[/os/windows/office/excel] permanent link

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo