Calendar

[cummingsea]

Excel Workbook
	B	C	D	E	F	G	H
2	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3	27	28	29	30	31	1	2
4	*	*	*	*	*	*	*
5	3	4	5	6	7	8	9
6	*	*	*	*	*	*	*
7	10	11	12	13	14	15	16
8	*	*	*	*	*	*	*
9	17	18	19	20	21	22	23
10	*	*	*	*	*	*	*
11	24	25	26	27	28	29	30
12	*	*	*	*	*	*	*
13	31	1	NOTES
14	*	*	*
15	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*
18	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*
20	*	*	1/1/2016	New Year's Day	*	*	*
21	*	*	1/18/2016	Martin L. King Jr. Day	*	*	*
22	*	*	2/15/16	Presidents' Day	*	*	*
23	*	*	3/27/2016	Easter	*	*	*
24	*	*	5/30/16	Memorial Day	*	*	*
25	*	*	7/4/2016	Independence Day	*	*	*
26	*	*	9/5/16	Labor Day	*	*	*
27	*	*	11/11/2016	Veterans Day	*	*	*
28	*	*	10/10/16	Columbus Day	*	*	*
29	*	*	11/24/16	Thanksgiving Day	*	*	*
30	*	*	12/25/2016	Christmas Day	*	*	*
January

is there a way using a formula that would enter New Years Day from Columns D20:E30 in cell G4 and using G3 as the date

 

[mole999]

Just a thought, if your cell that shows the date were actually the date i.e. 1/1/16 and formatted as d, then you would have the full value to cross reference with

 

[cummingsea]

it is a date formatted a D, any suggestions on a formula don't really want to use code

 

[Perpa]

it is a date formatted a D, any suggestions on a formula don't really want to use code

cummingsea,
The following formula in cell G4 will return the holiday name from column E as long as none of the holidays fall on the same numbered DAY:

=INDEX(Sheet1!$D$20:$E$30,SUMPRODUCT(--(DAY(Sheet1!$D$20:$D$30)=[COLOR="#FF0000"]G3[/COLOR]),ROW(Sheet1!$E$20:$E$30))-19,2)

You would need to copy it to the cell below each holiday DAY in each month. Presumably each month is on a different sheet and the holiday list is on Sheet1. Give it a try.
Perpa

 

[cummingsea]

It did not work it return #VALUE! I thought maybe the error was in the last part of your formula ROW(Sheet1!$E$20:$E$30)) -19,2 So I changed it to ROW(Sheet1!$D$20:$E$30)) -19,2 but I still got the same result #VALUE!

 

[Perpa]

It did not work it return #VALUE! I thought maybe the error was in the last part of your formula ROW(Sheet1!$E$20:$E$30)) -19,2 So I changed it to ROW(Sheet1!$D$20:$E$30)) -19,2 but I still got the same result #VALUE!

cummingsea,
I didn't have that problem when I ran it on my computer so I did a search on Excel using '#Value' and this is what I found when I looked under 'Index':

"Problem: The formula has not been entered as an array (Cntl+Sft+Enter).

If you are using INDEX as an array formula along with MATCH in order to be able to retrieve a value, you will need to convert your formula into an array formula, otherwise you will see a #VALUE! error."
So you might try entering the formula with CSE.
Perpa

 

[cummingsea]

Excel Workbook
	B	C	D	E	F	G	H
1	January 2016			*	*	*	*
2	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3	27	28	29	30	31	1	2
4	*	*	*	*	*	#VALUE!	*
5	3	4	5	6	7	8	9
Sheet1

as you can see the formula entered in G4 is an array formula but it's still giving me #VALUE! error
Thanks for your help guy's
original formula below same result as above

Excel Workbook
	B	C	D	E	F	G	H
1	January 2016
2	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3	27	28	29	30	31	1	2
4						#VALUE!
5	3	4	5	6	7	8	9
Sheet1