Find date in list, return static cell value

[kasper]

What I would like to do is compare a calendar like chart against a list of weekend dates and return the word "Weekend" in the chart for the dates that match.
I have already done a vlookup for a different worksheet that returns a W.
Excel 2010

	C	D	E
3		January	February
4	1
5	2

<tbody>
</tbody>

clean - COPY do not write on​

Worksheet Formulas Cell Formula D4 =IFERROR(VLOOKUP(D$3&$C4,INDIRECT($Q$3),2,0),IFERROR(VLOOKUP(D$3&$C4,Miscellaneous,2,0),"")) <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To Miscellaneous ='2015'!$Y$23:$Z$27 <tbody> </tbody>

<tbody>
</tbody>

Excel 2010

	B	C	D	E	F
14	Weekend		03-Jan-15	January3	W
15			04-Jan-15	January4	W
16			10-Jan-15	January10	W
17			11-Jan-15	January11	W
18			17-Jan-15	January17	W

<tbody>
</tbody>

holidays

​

Instead of altering my existing table D:F by adding the word "Weekend", I was hoping to simply return the single value from cell $B$14 every time a date in the calendar matches a date in E. Is there a formula that can do that?

 

[jackhandey]

I wonder if this might accomplish what you're trying to do a little more simply:

=IF(WEEKDAY(D2,2)>5,"True Condition","False Condition")

You wouldn't need a comparison chart at all if all you're trying to do is add a "Weekend" tag next to Saturdays and Sundays. Weekday() converts a date into a number 1 through 7 depending on the day of the week. The ",2" modifier makes it so that Monday = 1 through Sunday = 7. Therefore, if the value of WEEKDAY(D2,2) is greater than 5, it's either a Sat or a Sun, and then your Weekend condition applies.

Does this help? Or are weekends defined differently in your reference chart than a simple Sat/Sun logic?

 

[kasper]

I altered the formula (which I thought was fantastic BTW), but it seems to be recording the weekends incorrectly. Any idea why?
Excel 2010

	B	C	D	E
2		MAY
3		Morning	Lunch	After Work
4	1
5	2		W	W
6	3		W	W
7	4	Weekend
8	5	Weekend
9	6

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Lieu time log​

Worksheet Formulas Cell Formula C4 =IF(WEEKDAY($C$2&$B4,2)>5,"Weekend","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[jackhandey]

Hmmm. I duplicated your table and copied your formula into my C4 and copied down, and it correctly tagged May 2 and May 3 as weekend days.

Could you copy and paste here the exact formulas from cells C5 and C7?

 

[kasper]

C5 : =IF(WEEKDAY($C$2&$B5,2)>5,"Weekend","")
C7 : =IF(WEEKDAY($C$2&$B7,2)>5,"Weekend","")

 

[jackhandey]

I must admit, I'm stumped here. I copy your exact formulas from C5 and C7 into my C5 and C7 and they work perfectly.

Could it be an issue with manual calculation? To ensure automatic calculation is turned on, Formulas -> Calculation Options -> Automatic

Otherwise I think there has to be a discrepancy between the layout you show above and what is actually present. To check the actual inputs, try this:

In your C5 formula, highlight $C$2&$B5 and press F9 (to evaluate just that component). Does it return "MAY2"? If not, I think that's the breakdown here. If so... maybe someone smarter than I am can chime in. I don't believe there's a difference between how 2010 and 2013 evaluate the WEEKDAY formula, so I'm not sure what else would cause a different result for an identical formula + variables.

 

[kasper]

I tried as suggested and yes, F9 on $C$2&$B5 does evaluate to MAY2. The only thing I'm wondering is is it evaluating MAY2 *this* year? Is there a way to tell? Or to force it to evaluate 2015?

 

[jackhandey]

Well, let's make the date explicit and see if that fixes it. If you want to stick with the same setup you have now, I would replace $C$2&$B4 with this instead: DATE(2015,MONTH($C$2&1),B4)

in C4, obviously.

If you want the year to just be whatever year is current, replace 2015 with year(now()).

 

[jackhandey]

Also, to check how it is evaluating, you can just use a random formula somewhere to say =$C$2&$B5=date(2015,5,2) - if it evaluates to true, the date is evaluating to the current year. I believe it does by default, but it would be good to verify.

 

[kasper]

I don't know why or how, but it looks like this spreadsheet is NOT evaluating the current year. I get FALSE when using your above method of year evaluation.
I managed to rewrite your formula to force it to current year...
=if(weekday($C$2&" "&$B5&", "$Q$3,2)>5,"Weekend","") where $Q$3 is a drop-down year selector.
I appreciate all your help jackhandey!