Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mark
 
Posts: n/a
Default Need a ISWorkday Function -- Any Ideas

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.
  #2   Report Post  
James W.
 
Posts: n/a
Default

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

"Mark" wrote:

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.

  #3   Report Post  
N Harkawat
 
Posts: n/a
Default

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.

"Mark" wrote in message
...
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.



  #4   Report Post  
Duke Carey
 
Posts: n/a
Default

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



"Mark" wrote:

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.

  #5   Report Post  
Biff
 
Posts: n/a
Default

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

-----Original Message-----
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.
.



  #6   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Mon, 28 Mar 2005 09:43:02 -0800, "Mark"
wrote:

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


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.


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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hyperlinks using R[1]C[1] and offset function in its cell referenc Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 04:31 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM
Trim function doesn't clean out ASCII Code 160 (Space) Ronald Dodge Excel Worksheet Functions 6 January 27th 05 04:48 AM
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 01:25 AM
Counting Function Dilemma Simon Lloyd Excel Worksheet Functions 0 November 8th 04 04:13 PM


All times are GMT +1. The time now is 07:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"