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:
- Start_date - Required. A date that represents the starting date for a calculation
- num_days - Required. The number of nonweekend and nonholiday days before or after the start_date. A positive value yields a future date while a negative value yields a past date.
- holidays - Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.
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).
