Excel formula for week day work hours?  

So i have this excel formula to get the days of a month '=DATE(CONFIG!B3,CONFIG!D5,1)' I was wondering if there was a way to tag on a formula that based on if its a week day it would show the number of work hours (in this case 8) and if it was a weekend day it would show an X, any way to do this?

basically first row days of the month, which I got covered
second row right below each day the corresponding 8 or X respectively?

so when I change the month in the formula I wouldn't need to put the values for each day

any help is greatly appreciated!

Try3 said:

You can use the WeekDay function to return a number 1 to 7 then use an If <=5 for Monday - Friday to return 8 or X as required.

When combining formulas, it can be easier to write them in separate cells referring each other and only to combine them into a single longer formula after you have debugged each one.

It might be intentional but your code refers to the first of each month rather than any variable day within a month [unlike your Year & Month values which are picked up from cell values in your Config worksheet].

Denis

well I tried like you said , at least I think I did "=IF(WEEKDAY(F16)<=5,8,"X")" but its 1 day off, as in it shows the X value for the upper columns corresponding to Friday and Saturday, instead of the columns corresponding to Saturday and Sunday

Try3 said:

Sorry. I clearly did not pay attention when I tested it before posting.
Sunday is day 1
Monday is day 2
Tuesday is day 3
Wednesday is day 4
Thursday is day 5
Friday is day 6
Saturday is day 7

So the formula can be
=IF(WEEKDAY(A3)>1,IF(WEEKDAY(A3)<=6,8,"X"),"X")

Denis

oh yes that works out great many thanks!

any chance I could also trouble you for a conditional format rule, to change the color of the cells that turn out to be saturdays and sundays?

Try3 said:

Excel 2007 illustration. Yours might look a bit different.


Conditional formatting,
New rule,
Format only cells that contain ...
Cell value - equal to - X
Format …

I always start any conditional formatting from the New rule choice because I often get hopelessly confused by the other choices.

I find the examples in this thread helpful
Conditional formatting - multiple refs including to other cells - TenForums

Denis

many thanks! you simply the best!