Lookup date within range, then concatenate results into single cell

[stevebnoise]

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.

Thanks!

 

[Andrew Poulsom]

Welcome to MrExcel.

Sorry, but you can't apply rich text formatting (eg highlighting) to the result of a formula.

 

[stevebnoise]

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.

 

[Andrew Poulsom]

Concatenation is difficult in Excel. Can't you just AutoFilter your data on columns E and F to get the list that you want?

 

[stevebnoise]

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.

 

[Andrew Poulsom]

Filter column E for less than or equal and column F for greater than or equal to the date you want to use.

 

[stevebnoise]

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.