Excel 2010 Workday and Weekday Formula

[Chris Waller]

Hi,

I have a Worksheet that contains a list of colleagues and their annual leave. The dates are horizontal and they are all formulas referring to a date in cell B1. The formulas are in the format of =workday(C4,1). Some of these formulas are formatted as ddd to show the day of the week. What I want to do is create a formula or VBA that will put NWD (non working day) in the cells for each Thur or Fri. One of my colleagues doesn't work every other Monday. I was jsut wondering what is the best and easiest way to go about performing both these tasks? TIA

 

[SG2001]

Not sure I completely understand but for the Thu, Fri one would something like the following work (I just used J8 as the date cell)? This would return NWD for any date which is Thu or Fri and return the day of week for the others. If you would want something else besides day of week, say blank, you could put "" where the last WORKDAY formula is located.

=IF(OR(TEXT(WORKDAY(J8,0),"DDD")="Thu",TEXT(WORKDAY(J8,0),"DDD")="Fri"),"NWD",WORKDAY(J8,0))

 

[Chris Waller]

SG2001,

Thanks for the prompt reply. I did as you suggested and put the blank in as you said. It does appear to work, although I am at home at the moment and unable to try it out until Monday. Thanks again for your help. It is much appreciated.

 

[SG2001]

This might work for the Monday part of question. You will have to manually enter NWD for the first Monday if it was a day off.
=IF(TEXT(WORKDAY(T8,0),"DDD")<>"Mon","",IF(OFFSET(T8,6,-7)="NWD","","NWD"))

 

[Chris Waller]

SG2001,

Thanks for the second part of the formula. I haven't tried it yet but I will try it tomorrow when I get to work. I will let you know the outcome. Thanks again for your help.

 

[Chris Waller]

SG2001,

I did try the formula and it appears to work in part. For example it is highlighting every monday as a NWD (Non Working Day) rather than every other Monday. I just cannot work out the second part of the formula is doing from, IF(Offset etc, etc. I think T8 is referencing the cell that contains the day of the week. I just cannot make out what the 6 and the -7 are doing. The information is being input on row 9 on the Spreadsheet and thit will be input on column B and dragged through to column IG. Thanks once again.