Lookup date within range, then concatenate results into single cell


New Member
Apr 15, 2013
I am attempting to make a vacation calendar.

I am looking to take a date cell and check if it is within a user entered date range. If so, I want it to return and concatenate all employees that were on vacation for that day, highlighting paid vacation green and unpaid red. I am having 2 main issues. I cannot seem to place a criteria to only pull vacation that is not cancelled and I cannot seem to create a formula that will match the criteria and concatenate all returned values, then highlight it. However, I am leaning towards removing the cancellation entry type and instead just deleting/ altering entries whenever a cancellation request is made.

I have an example document that resembles what I am working on and am willing to send it to anyone who may be able to help. Ideally, this will allow the user to select the month on the calendar and it will be updated automatically with data pulled from the Vac Days sheet (Vacation table).

I am open to any and all suggestion as I have been beating my head against this project for a few days. Also, I am not against VBA, but consider myself green and learning.


Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Welcome to MrExcel.

Sorry, but you can't apply rich text formatting (eg highlighting) to the result of a formula.
Upvote 0
I figured as much. If I remove the formatting aspect, is this something that can be done?

In a nutshell, I want to see if a date is between 2 other dates (column E -end date, column f -start date), if it is paid vacation, and if it is return and concatenate all values in column a that match that criteria.

For example, let's say I am looking at 4/1/2013

Column A Column E Column F Column G
John Doe 3/31/2013 4/4/2013 Paid Vacation
Jane Doe 4/2/2013 4/5/2013 Paid Vacation
Other Guy 4/1/2013 4/5/2013 Unpaid Vacation

I would like a formula that would use the 4/1/2013 and return all paid vacation values in column a where 4/1/2013 is between the dates in E and F. Using this example, the only return would be John Doe.
Upvote 0
Concatenation is difficult in Excel. Can't you just AutoFilter your data on columns E and F to get the list that you want?
Upvote 0
Yeah, it is proving to be much more difficult than anticipated. I have considered Autofilter, but I wasn't able to set the filter to capture what I am looking for. I will give that another go, but do you have any suggestions on how to capture that information with the filter? At the end of the day, I would need to be able to see any time off taken within a 2 week period for payroll processing.
Upvote 0
THANK YOU! I had been trying autofilter, but was doing it backwards from what you suggested. Your way picked up every employee with vacation, allowing me to use the existing table to filter down to what I needed. I wish I could find an easier to read solution, but I am happy to finally be at a point of being able to get what I need from the table.

Thanks for your help.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back