Is it a Workday?

[excelnutter45]

Hi there,

I’ve had a good look around for an answer to this but no luck! Hope somebody can help. I’m familiar with using the workday function to work out what the date is it in 6 working days time, however what I can't work out is how I can evaluate whether a date is a working day on not?

My problem is this: if I start a task today (9th sept - Tuesday) I need to know whether or not the date in exactly a month's time - 9th Oct – is going to fall on a workday or not, in this particular case it is does, so it should return 9th Oct (Thursday) ok. But if I did the same on the 11th sept it should return the 13th Oct as the 11th Oct is a Saturday.

I’m ok in adding a month to a date as it is it simply =(year(A1), Month(A1)+1,Day(A1)). But is it just the evaluating where I become stuck.

I'm probably missing something really obvious!!!!!!!!!! Any help greatly appreciated.

Paul

 

[dave3009]

Hi

You might want to look at the WEEKDAY function it will return 7 for a saturday and 1 for a sunday.

=WEEKDAY(date)

KR


Dave

 

[VoG]

Does this help?

=IF(AND(WEEKDAY(A1)>1,WEEKDAY(A1)<7),"week","weekend")

 

[barry houdini]

If you want A1+1 month....but always the next workday if that isn't one....

=WORKDAY(EDATE(A1,1)-1,1)

format as date

 

[lucianot]

I have the same question as the poster.

The answers suggested only work to check whether it's a WEEKDAY or not. However, if it falls on a holiday during a weekday, it will not work.

What I do manually is get the next workday using WORKDAY(date, -1, holidays), then go back 1 workday using WORKDAY(date, -1, holidays). If the result is equal to the original date, then it is a workday. Obviously there is a easier way of doing this...

 

[xld]

=WORKDAY(EDATE(A1,1)-1,1,holiday_range)

 

[lucianot]

I believe some of you are missing the point. The idea is to check whether a given date is a workday or not. The above formula only moves the date to a workday next month...

 

[barry houdini]

I believe some of you are missing the point. The idea is to check whether a given date is a workday or not.

Whose idea?

Excelnutter wanted a formula that would add 1 month....but move to the next workday if the resultant date was a weekend. The formula I suggested will do exactly that, i.e. with

=WORKDAY(EDATE(A1,1)-1,1)

....then if A1 is 9th September 2008 the formula returns 9th October 2008 as required but if A1 is 11th September then the formula returns 13th October 2008 also as required. If you want to exclude holidays as well then you can add a holiday range to the above as xld suggests.

To achieve the desired result you don't need to explicitly check whether EDATE(A1,1) is a workday or not.

If you want a formula that will check whether a given date is a workday or not you could use this, where your date is in D2

=IF(NETWORKDAYS(D2,D2,holidays),"workday","not a workday")

 

[lucianot]

Sorry Barry, you're right about the original poster. Thanks for the formula.