I need to verify if Christmas falls within a date range. My best guess is the following formula:
=OR(IF(DATE(YEAR(StartDate),12,25)>StartDate,1,0),IF(DATE(YEAR(EndDate),12,25)<EndDate,1,0))*1
The problem I have is when the date range falls between two years. How can I make it check the total number of christmas included within the date range.
Thanks.
Are you looking for a formula that returns the total number of Christmases between a date range?
One way...
A2 = start date
B2 = end date
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A2&":"&B2)),"mmmdd")="Dec25"))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Another way...
=YEAR(EndDate)-YEAR(StartDate)-IF(AND(MONTH(StartDate)=12,DAY(StartDate)>25),1,0)+IF(AND(MONTH(EndDate)=12,DAY(EndDate)>=25),1,0)
Depending on what you're trying to do with the function, NETWORKDAYS already has most of the US holidays programmed in the back end to it, so you could compare NETWORKDAYS and WORKDAYS to determine if there's a holiday in there.
Going for Guru! Click the Star to the bottom left of this post if I helped!
Thank you all for your help. I will test all of your suggestions.
And another one...
=YEAR(B2)-YEAR(A2)+(A2<=DATE(YEAR(A2),12,25))-(B2<DATE(YEAR(B2),12,25))
I tested NETWORKDAYS, but this gives you a total count of days. I only need to find the days off. I will also use this formula to test for total number of Thanksgiving holidays included in the range.
To LarryLegend
While testing for Thanksgiving...
For startdate: Feb 14 / 2012
and enddate: Dec 20 / 2012
Your modified formula
=YEAR(EndDate)-YEAR(StartDate)-IF(AND(MONTH(StartDate)=11,DAY(StartDate)>26),1,0)+IF(AND(MONTH(EndDate)=11,DAY(EndDate)>=26),1,0)
gives me zero holidays
For counting Thanksgiving, assuming 4th Thursday in November, try this twist on Biff's suggestion
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A2&":"&B2)),"mmm-ddd-d")="Nov-Thu-"&{22,23,24,25,26,27,28}))
Audere est facere
Icyriuis - Based on your desciption, I thought you were looking only for Xmas, not other holidays. If you change the = in front of the month to >=, the formula will work for you to test other holidays like Thanksgiving. Be careful, because with a holiday like Thanksgiving, the actual date it falls on changes every year until Xmas, 4th of July, Valentine's Day, etc.
=YEAR(EndDate)-YEAR(StartDate)-IF(AND(MONTH(StartDate)>=11,DAY(StartDate)>26),1,0)+IF(AND(MONTH(EndDate)>=11,DAY(EndDate)>=26),1,0)
To Tony Valko,
Both formulas provided worked. Thanks.
To LarryLegend
Thanks for the heads up. I have two holidays like that Easter and Thanksgiving.
Total days - Total working days = Total days off.Originally Posted by icyrius
See my version for counting Thanksgiving..... Easter is a bit trickier......
Do you want to count these separately or get a total count of holidays within the date range? If it's the latter then you could just create a list of all holidays and use NETWORKDAYS.INTL , i.e. for a count of total holidays within the range
=B2-A2+1-NETWORKDAYS.INTL(A2,B2,"0000000",holidays)
Looks like they're using Excel 2007 so the NETWORKDAYS.INTL function isn't available to them.
Count of Easter holidays is going to be brutal!
You're welcome. Thanks for the feedback!
True, I read the wrong profile......
This would work for Easter Sundays
=YEAR(B2)-YEAR(A2)+(A2<=FLOOR(DATE(YEAR(A2),5,DAY(MINUTE(YEAR(A2)/38)/2+56)),7)-34)-(B2<FLOOR(DATE(YEAR(B2),5,DAY(MINUTE(YEAR(B2)/38)/2+56)),7)-34)
For Easter Mondays change the 34s to 33s and for Good Fridays that would be 36
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks