Need a ISWorkday Function -- Any Ideas

M

Mark

Simply put, I need a function that will indicate if a given date is a workday
or a weekend. I could also use a function that would given me the
networkdays in a given month, without having to specify a start and end date.
If you have any ideas, please let me know. What follows is a detailed
explanation of what I am trying to do which may or may not be helpful.
Thanks in advance!




I am working on a sheet that requires an IsWorkday function. Essentially, I
have a function that counts the number of days in a given month which uses
the NetWorkDays function.

This function was created so that a user can specify a start date, and the
sheet will fill out the number of days within each month from that date.

To do this, I have the first date in the NetWorkDays function as the first
day of a given month. The second date is the first day of the following
month.

The problem is that the function, as it currently stands, is often off by 1
day because it is counting the first day of the following month. I can't
simply make the second day of the NetWorkDays function the last day of the
month, because I don't know if the month will have 28, 30, or 31 days.

Essentially, I need the IsWorkday function so that I can put a constraint on
my existing function.
 
J

James W.

Try something like this.... =IF(OR(WEEKDAY(C15)=1,WEEKDAY(C15)=7),"Weekend",
"Workday")
 
N

N Harkawat

Why not add this to your existing formula which is adding an extra day to
the first day of next month as followis:-
=IF(WEEKDAY(A1,2)>5,0,-1)
It will give a -1 if first day of next month is a weekend and your result
should be correct.
 
D

Duke Carey

Why not use the EOMONTH() function to determine the last day of the month

Alternatively, use Date(year, month+1,1)-1 to determine the first day of
the next month and then subtract a day to let Excel calculate the last day of
the target month
 
B

Biff

Hi!

For total workdays in a given month with the first day of
the month entered into A1:

A1 = 3/1/2005


=NETWORKDAYS(A1,EOMONTH(A1,0))

To determine if a given date is a weekday or weekend with
Monday thru Friday being the traditional "weekdays" and
Monday being the FIRST day of the week:

=WEEKDAY(A1,2)<6

Will return a TRUE or FALSE. If you want some other
designation just put that into an IF function:

=IF(WEEKDAY(A1,2)<6,"Weekday","Weekend")

Biff
 
R

Ron Rosenfeld

Simply put, I need a function that will indicate if a given date is a workday
or a weekend.
Click to expand...

With some date in F1, the formula:

=WORKDAY(F1-1,1,HOLIDAYS)=F1

will return TRUE if F1 is a workday, and FALSE if F1 is not a workday.
HOLIDAYS is a range which includes an optional list of HOLIDAYS. If that is
not relevant, that term can be omitted.

If the WORKDAY function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.



I could also use a function that would given me the
networkdays in a given month, without having to specify a start and end date.
Click to expand...

With some date in the given month in F1,

=networkdays(F1-DAY(F1)+1,eomonth(F1-DAY(F1),1),HOLIDAYS)

will do what you request.




--ron
 
Top