Hello,
I'm trying to find a way to look a holiday if it falls between two dates, and return the holiday name in another cell.
I'm attaching the excel file, thanks for help if anyone have suggestions.
Thanks
Hello,
I'm trying to find a way to look a holiday if it falls between two dates, and return the holiday name in another cell.
I'm attaching the excel file, thanks for help if anyone have suggestions.
Thanks
This formula does what you specified but do you really just want to check a single holiday against a single pair of dates? Or do you want to match a whole list of holidays?
Formula:Please Login or Register to view this content.
Thanks a lot, Actually NO, I wanted to look into a list of holidays for sure, I just included the excel, with one row, I might have needed to explain more
Holiday list is available in other workbook. So upload that also.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Ok, for a better explanation of the solution I'm searching for, I have attached the right workbook with the holidays list dates and names
So what I need to find out is:
If a Holiday Day falls between start date C and End date D, then show the Holiday name in A and relative date in B
Holidays list and names are in Sheet "Holidays" and named holiday_dates & holiday_names
Thank you all, for helping.
For many of these ranges there are multiple holidays. For example, in row 3 there are 14 holidays in that range. How do you want to handle that?
Yes that's another challenge, maybe to have the start and end dates of this range or just the 1st date in this range! That's so challenging.
Or maybe if there are many, then return "Long Holiday"
This version shows the count of holidays in the range and the date and name of the first one.
OMG! That's Amazing! Thanks a lot 6StringJazzer, That solves the issue, by adding Holidays count, I was struggling to find all this, I will learn how it works. Happy Holidays
Any way to exclude those holidays named EXTRA from being seen by excel? Using VBA I can hide or show them but they are still there, just hidden!
I'm trying to find a way to exclude those days from the list, but show them again if needed!
Perhaps the following will help.
1. Place the word EXTRA in cell G1
2. Modify the formula in the holiday count column to read: =COUNTIFS(Holidays!holiday_dates,">="&$C2,Holidays!holiday_dates,"<="&$D2,holiday_names,"<>"&G$1)
3. Modify the formula in the holiday date column to read: =IF(E2>0,INDEX(Holidays!holiday_dates,AGGREGATE(15,6,(ROW(holiday_names)-ROW(A$1))/(holiday_names<>G$1)/(Holidays!holiday_dates>=C2),1)),"")
4. [Optional] Modify the formula in the holiday name column to read: =IF(B2="","",INDEX(holiday_names,MATCH(Sheet5!B2,Holidays!holiday_dates,0)))
Note that when the word EXTRA is deleted from cell G1 it will again be shown in the holiday name column.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks