b***@does.not.exist.com
2007-10-18 13:49:24 UTC
The result is a date number that can be formatted as a date. You then see
the date of a day that is a certain number of *Workdays* away from the *Start
date*.
Syntax
WORKDAY (Start date;Days;Holidays)
*Start date*: the date from when the calculation is carried out. If the
start date is a workday, the day is included in the calculation.
*Days*: the number of workdays. Positive value for a result after the start
date, negative value for a result before the start date.
*Holidays*: list of optional holidays. These are non-working days. Enter a
cell range in which the holidays are listed individually.
--------
Someone else's reply:
In case you are using a spreadsheet that doesn't have the WORKDAY function
but does have a WEEKDAY function, assuming that your due date will be on a
business day, then the formula for four days before the value in cell A3 is
=IF(WEEKDAY(A3)=6,A3-4,A3-6)
If you have due dates on non-business days, then you would need to nest an
additional if statement for the Saturday and Sunday due dates
=IF(WEEKDAY(A3)=6,A3-4,IF(WEEKDAY(A3)=7,A3-5,A3-6))
------=_Part_11850_20268220.1192905128735
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Below is the email asking for help, then my response, then the response from what I assume is an Excel user.<br><br>OO.o FTW!<br><br><span class="q"><span class="gmail_quote">On 10/20/07, Judy wrote</span></span><span class="q" id="q_115be222661b010f_3">
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I have read the help in Excel and still don't know how to do the following:
<br><br>I want to put in a due date and in another cell put in the date that is<br>4 BUSINESS days earlier than that. I can subtract day, but not<br>business days, even though they have an "autofill" that will put in
<br>only business days.<br><br>Anyone know how to do this?</blockquote><div><br></div></span><span class="q">Judy,<br><br>I don't have Excel installed, but I do use OpenOffice, and
it is a simple one command: WORKDAY. I assume there is something
similar in Excel.<br><br>Syntax: assume A1 has due date.
=WORKDAY(A1;-4) You can also add known holidays as a third input, but
it isn't required (the help tells you about that).
<br><br>Best,<br><br>Greg Grossmeier<br><br>From the help file:<br>
<h2><a name="115be222661b010f_115be1c871ed1916_bm_id3149012"></a><a name="115be222661b010f_115be1c871ed1916_bm_id3150028"></a><a name="115be222661b010f_115be1c871ed1916_workday"></a>
WORKDAY
</h2>
<p>The result is a date number that can be formatted as a date. You
then see the date of a day that is a certain number of <b>Workdays</b>
away from the <b>Start date</b>.</p>
<h3>Syntax</h3>
<p>WORKDAY (Start date;Days;Holidays)</p>
<p><b>Start date</b>: the date from when the calculation is carried
out. If the start date is a workday, the day is included in the
calculation.</p>
<p><b>Days</b>: the number of workdays. Positive value for a result
after the start date, negative value for a result before the start
date.</p>
<p><b>Holidays</b>: list of optional holidays. These are non-working
days. Enter a cell range in which the holidays are listed
individually.</p>--------<br></span>Someone else's reply:<br><br><font face="sans-serif" size="2">In case you are using a spreadsheet
that doesn't have the WORKDAY function but does have a WEEKDAY function,
assuming that your due date will be on a business day, then the formula
for four days before the value in cell A3 is</font>
<br>
<br><font face="sans-serif" size="2">=IF(WEEKDAY(A3)=6,A3-4,A3-6)</font>
<br>
<br><font face="sans-serif" size="2">If you have due dates on non-business
days, then you would need to nest an additional if statement for the Saturday
and Sunday due dates</font>
<br>
<br><font face="sans-serif" size="2">=IF(WEEKDAY(A3)=6,A3-4,IF(WEEKDAY(A3)=7,A3-5,A3-6))</font>
<br>
------=_Part_11850_20268220.1192905128735--
the date of a day that is a certain number of *Workdays* away from the *Start
date*.
Syntax
WORKDAY (Start date;Days;Holidays)
*Start date*: the date from when the calculation is carried out. If the
start date is a workday, the day is included in the calculation.
*Days*: the number of workdays. Positive value for a result after the start
date, negative value for a result before the start date.
*Holidays*: list of optional holidays. These are non-working days. Enter a
cell range in which the holidays are listed individually.
--------
Someone else's reply:
In case you are using a spreadsheet that doesn't have the WORKDAY function
but does have a WEEKDAY function, assuming that your due date will be on a
business day, then the formula for four days before the value in cell A3 is
=IF(WEEKDAY(A3)=6,A3-4,A3-6)
If you have due dates on non-business days, then you would need to nest an
additional if statement for the Saturday and Sunday due dates
=IF(WEEKDAY(A3)=6,A3-4,IF(WEEKDAY(A3)=7,A3-5,A3-6))
------=_Part_11850_20268220.1192905128735
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Below is the email asking for help, then my response, then the response from what I assume is an Excel user.<br><br>OO.o FTW!<br><br><span class="q"><span class="gmail_quote">On 10/20/07, Judy wrote</span></span><span class="q" id="q_115be222661b010f_3">
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I have read the help in Excel and still don't know how to do the following:
<br><br>I want to put in a due date and in another cell put in the date that is<br>4 BUSINESS days earlier than that. I can subtract day, but not<br>business days, even though they have an "autofill" that will put in
<br>only business days.<br><br>Anyone know how to do this?</blockquote><div><br></div></span><span class="q">Judy,<br><br>I don't have Excel installed, but I do use OpenOffice, and
it is a simple one command: WORKDAY. I assume there is something
similar in Excel.<br><br>Syntax: assume A1 has due date.
=WORKDAY(A1;-4) You can also add known holidays as a third input, but
it isn't required (the help tells you about that).
<br><br>Best,<br><br>Greg Grossmeier<br><br>From the help file:<br>
<h2><a name="115be222661b010f_115be1c871ed1916_bm_id3149012"></a><a name="115be222661b010f_115be1c871ed1916_bm_id3150028"></a><a name="115be222661b010f_115be1c871ed1916_workday"></a>
WORKDAY
</h2>
<p>The result is a date number that can be formatted as a date. You
then see the date of a day that is a certain number of <b>Workdays</b>
away from the <b>Start date</b>.</p>
<h3>Syntax</h3>
<p>WORKDAY (Start date;Days;Holidays)</p>
<p><b>Start date</b>: the date from when the calculation is carried
out. If the start date is a workday, the day is included in the
calculation.</p>
<p><b>Days</b>: the number of workdays. Positive value for a result
after the start date, negative value for a result before the start
date.</p>
<p><b>Holidays</b>: list of optional holidays. These are non-working
days. Enter a cell range in which the holidays are listed
individually.</p>--------<br></span>Someone else's reply:<br><br><font face="sans-serif" size="2">In case you are using a spreadsheet
that doesn't have the WORKDAY function but does have a WEEKDAY function,
assuming that your due date will be on a business day, then the formula
for four days before the value in cell A3 is</font>
<br>
<br><font face="sans-serif" size="2">=IF(WEEKDAY(A3)=6,A3-4,A3-6)</font>
<br>
<br><font face="sans-serif" size="2">If you have due dates on non-business
days, then you would need to nest an additional if statement for the Saturday
and Sunday due dates</font>
<br>
<br><font face="sans-serif" size="2">=IF(WEEKDAY(A3)=6,A3-4,IF(WEEKDAY(A3)=7,A3-5,A3-6))</font>
<br>
------=_Part_11850_20268220.1192905128735--