CODEWITHSUNDEEP
excelexcel-formulavba
Kzhel Farmer
Kzhel Farmer

Reputation: 112

Cell value in calendar instead of Highlight

I am having a leave calendar which is highlighting the dates as per employee leave date in calendar using a conditional formatting which is working perfect but i want instead of highlight it display Name mentioned in front of date into the calendar.

=SUMPRODUCT((B3<=$I$19:$I$150)*(B3>=$E$19:$E$150))=1

cal

I want to show name in calendar instead of green highlight. Formula or VBA; anything is acceptable.

Upvotes: 1

Views: 988

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

In B3 put this:

=IFERROR(INDEX($A$20:$A$151,MATCH(1,IF(DATE($D$1,ROWS($A$3:$A3),B$2)>=$E$20:$E$151,IF(DATE($D$1,ROWS($A$3:$A3),B$2)<=$I$20:$I$151,1)),0)),"")

This is an array formula and must be confirmed with Ctrl-Shift-Enter. Then Copy Down and Across.

enter image description here

If the name is only necessary in the first day of the holiday period then a simpler INDEX/MATCH function pair will suffice.

In B3 as, 

=IFERROR(INDEX($A$20:$A$150,  MATCH(DATE(YEAR($A3), MONTH($A3), B$2), $E$20:$E$150, 0)), "")

Fill right and down.

  holiday_planner_calendar2

Notes:

These will only return the first name if there are overlaps.

Depending on your local seetings you may need to change the delimiter from , to ;.

If you still want you conditional formatting then change the conditional formatting formula to:

=B3<>""

Upvotes: 2

Related Questions