workday function

  • Thread starter Thread starter Todd
  • Start date Start date
Hi,

How do I get excel to calculate the first business day of
a period?

TIA


Todd
Click to expand...

First you have to define 'period'. Week, month, quarter, year, decade, ????

Then would the first business day be the first weekday after the start? The
first Monday of the week which contains the start?

In your country, do business days include Saturdays?

For example, if a period means a week, and if you use the ISO week numbering
scheme, and Saturday is not a business day, then the first business day in a
period could be given by the formula:


=workday(DATE(YYYY,1,4)-WEEKDAY(DATE(YYYY,1,4),3)+7*(WeekNumber-1)-1,1,Holidays)

YYYY is the year
WeekNumber is the number of the period you are interested in.
Holidays is a named range that contains a list of the Holidays.

You need to have the Analysis ToolPak installed, or else you will get a #NAME
error.


--ron
 
Thank you,

I am trying to calcualate the date employee benefits would
start. It would be two periods in a month
one is on/before the 15th and the other is after the 15th.

So if qualified for benefits on/before the 15th what would
be the first business day of that month and if qualified
later what would the first business day after the 15th.

I have a seperate cell containing the dates and this is
what I had (of course it doesn't work).

=IF(DAY(H12)>15,H15-DAY(H12)+16,H12-DAY(H12)+1)

I tried replacing the day with work day but it returns #N/A

=IF(WORKDAY(H12)>15,H15-WORKDAY(H12)+16,H12-WORKDAY(H12)+1)

any ideas how to set this up better?


Todd
 
Thank you,

I am trying to calcualate the date employee benefits would
start. It would be two periods in a month
one is on/before the 15th and the other is after the 15th.

So if qualified for benefits on/before the 15th what would
be the first business day of that month and if qualified
later what would the first business day after the 15th.

I have a seperate cell containing the dates and this is
what I had (of course it doesn't work).

=IF(DAY(H12)>15,H15-DAY(H12)+16,H12-DAY(H12)+1)

I tried replacing the day with work day but it returns #N/A

=IF(WORKDAY(H12)>15,H15-WORKDAY(H12)+16,H12-WORKDAY(H12)+1)

any ideas how to set this up better?


Todd
Click to expand...

OK, if I understand you correctly you have a date which could be in one of two
groups: either it is in the 1-15th of the month; or it is in the 16th to Last
Day of month.

Given that date, you want to know the first business day of the month, and I
would assume that the first business day should not be a Saturday, Sunday or
Holiday.

Make sure that what happens when the qualification date falls on a weekend is
what you expect. For example, if the qualification date for an employee were
2/16/2003 (Sunday), then the first business date in the appropriate period
would be 2/17/2003 (Monday).

With your qualification date in A1, the first workday of the semi-monthly
period is:

=WORKDAY(DATE(YEAR(A1),MONTH(A1),15*(DAY(A1)>15)),1,Holidays)

The WORKDAY function requires that the Analysis Tool Pack be installed or else
you will get a #NAME error.

Holidays is a named range containing the dates of your holidays.

If you are not using a US version of Excel, carefully check the results as the
Analysis Tool Pack is rather US-Centric. I do have a VBA routine that will
work, but it runs much more slowly.







--ron
 
OK, if I understand you correctly you have a date which could be in one of two
groups: either it is in the 1-15th of the month; or it is in the 16th to Last
Day of month.

Given that date, you want to know the first business day of the month,
Click to expand...
^^^^^
Oops, the above should read period

and I
would assume that the first business day should not be a Saturday, Sunday or
Holiday.

Make sure that what happens when the qualification date falls on a weekend is
what you expect. For example, if the qualification date for an employee were
2/16/2003 (Sunday), then the first business date in the appropriate period
would be 2/17/2003 (Monday).

With your qualification date in A1, the first workday of the semi-monthly
period is:

=WORKDAY(DATE(YEAR(A1),MONTH(A1),15*(DAY(A1)>15)),1,Holidays)

The WORKDAY function requires that the Analysis Tool Pack be installed or else
you will get a #NAME error.

Holidays is a named range containing the dates of your holidays.

If you are not using a US version of Excel, carefully check the results as the
Analysis Tool Pack is rather US-Centric. I do have a VBA routine that will
work, but it runs much more slowly.
Click to expand...

--ron
 
Ok, I am getting to understand all this. Thanks. I have
a similar situation the ends in the same question.

These dates have two options. Either I use the first
business day of the current month or the next one. If the
start date day is 01 then I use the first business day of
that month. All other dates become the first business day
of the next month.

I posted my formula below and its results. It works
Except that it returns the first day of the month if the
day in C1 =1 . Not the first business day.


=IF(DAY(C1)=1,WORKDAY(EOMONTH($C1,0),1),WORKDAY(EOMONTH
(C1,0),1))


Thanks!

Todd

Start Date Resulting Start Date

11/1/87 12/1/87
8/1/83 8/1/83
5/16/2001 6/1/01
5/1/2003 5/1/03
7/3/1996 8/1/96
 
Ok, I am getting to understand all this. Thanks. I have
a similar situation the ends in the same question.

These dates have two options. Either I use the first
business day of the current month or the next one. If the
start date day is 01 then I use the first business day of
that month. All other dates become the first business day
of the next month.

I posted my formula below and its results. It works
Except that it returns the first day of the month if the
day in C1 =1 . Not the first business day.


=IF(DAY(C1)=1,WORKDAY(EOMONTH($C1,0),1),WORKDAY(EOMONTH
(C1,0),1))


Thanks!

Todd

Start Date Resulting Start Date

11/1/87 12/1/87
8/1/83 8/1/83
5/16/2001 6/1/01
5/1/2003 5/1/03
7/3/1996 8/1/96
Click to expand...

Did the previous formula do what you want?

========================================

With regard to your current problem, I think your formula may have another
error, or I am not understanding you correctly. I don't understand why 11/1/87
returns 12/1/87. From your description it should return the first business day
in November, not December.

In any event, to return the first business day in the Start Date month if the
date is on the first of the month, and the first business day in the subsequent
month, if the date is not on the first of the month, try this formula:

=workday(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>1),0),1,Holidays)

Again, Holidays is a named range with the dates of all the holidays.

--ron
 
Back
Top