How do I get excel to calculate what day a bank holiday will be.
for example: new years day is 01/01/YEAR
if this is saturday 01/jan the bank holiday would be monday 03/jan
This is to be a dynamic calendar and so it auto populates based on the year chosen.
Can you also tell me how to do this for august bank holiday, last monday in august.
thanks,
andy
Andy, my point would remain the same...
http://www.mrexcel.com/forum/showthread.php?t=473654
Did you check out the link I provided re: calculation of Easter Public Holiday - it's really not as straightforward as I think perhaps you believe it to be.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I think this will work for you.....
New year bank holiday
August bank holiday
Please Login or Register to view this content.
Please Login or Register to view this content.
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
Hi with the new years day statement it is returning new years day bank holiday for january as tuesday 4th 2010. when it should be monday 3rd is this correction right or does the 2 stand for saturday and sunday?
=DATE(I4,1,1+((1-(2>=WEEKDAY(DATE(I4,1,1))))*7)+(1-WEEKDAY(DATE(I4,1,1))))
I4 = year
Looks OK to me. Sorry for the error.
Tried this for christmas and your first formula was correct, so i am not sure now. Would some form of if statement work better.
E.g. if date(year,1,1) is not a weekday,workday(date(year,1,1),1), date(year,1,1)
not sure how to do the is not a weekday?
also with boxing day how would this work?
eg. christmas sat 25dec using same as above would be 27th dec
but if same as above was used for boxing day sun 26dec it would also return 27th.
how would I return it to take in to account the other bank holiday?
Also would this then work if christmas was sun25th therefore would be mon26th and boxingday would be tuesday 27th?
as outlined at MrExcel.com re: Christmas & New Year...
Boxing Day
if falls on Sat or Sun then result is 28th, if Mon then result is 27th else 26th.
Christmas Day
for Christmas Day if Sat then 27th, if Sunday then 26th else 25th.
You have the WEEKDAY function to hand already so you're aware of this - you can use this in conjunction with a CHOOSE or LOOKUP to adjust appropriately - eg:
=DATE(2010,12,26)+LOOKUP(WEEKDAY(DATE(2010,12,26),2),{1,2,6},{1,0,2})
obviously the ref. to the Date would be a variable of some sort (how you're specifying this is unknown)
Last edited by DonkeyOte; 06-10-2010 at 09:15 AM.
Generic formula for nth xday in month
=DATE(year,mon,1+7*n)-WEEKDAY(DATE(year,mon,8-x))
where x represents day of week (1 = Sun through to 7 = Sat)
Using this, for example, you can get US thanksgiving for 2010 (4th Thursday in November) with this formula
=DATE(2010,11,29)-WEEKDAY(DATE(2010,11,3))
or for last Monday in August you can use the above for 1st Monday in September and then subtract 7, i.e.
=DATE(2010,9,1)-WEEKDAY(DATE(2010,9,6))
Obviously you can make the year dynamic......
Audere est facere
How would i calculate the first monday in a month? is that weekday(2010,5,1) ?
Last edited by mcinnes01; 06-10-2010 at 11:16 AM.
Would this be correct:
=WORKDAY(DATE($I$4,5,1),1)
I think dll outlined this, ie for 1st Monday in May 2010:
=DATE(2010,5,1+7*1)-WEEKDAY(DATE(2010,5,8-2))
that in red is "n" - ie "nth" occurrence of given weekday in the month of interest
that in blue is weekday of interest (based on default WEEKDAY where 1 is Sun and 7 is Sat)
ok so i've spent some time testing and have found a few errors....
=DATE($I$4,5,1+7*1)-WEEKDAY(DATE($I$4,5,8-2))
This formula is correct for early may bank holiday
=DATE($I$4,6,1)-WEEKDAY(DATE($I$4,6,6))
This formula does not work for spring bank holiday, when i got to 2013 it said 28thmay when in fact its is the 4 of june.
Any ideas as to what i need to put for the spring bank holiday?
=IF(MONTH(DATE($I$4,5,1+((1-(2>=WEEKDAY(DATE($I$4,5,1))))*7)+(2-WEEKDAY(DATE($I$4,5,1))))+28)=8,DATE($I$4,5,1+((1-(2>=WEEKDAY(DATE($I$4,5,1))))*7)+(2-WEEKDAY(DATE($I$4,5,1))))+28,DATE($I$4,5,1+((1-(2>=WEEKDAY(DATE($I$4,5,1))))*7)+(2-WEEKDAY(DATE($I$4,5,1))))+21)
this doesn't seems to be working correctly 2010
but works correctly for summer bank holiday in:
=IF(MONTH(DATE($I$4,8,1+((1-(2>=WEEKDAY(DATE($I$4,8,1))))*7)+(2-WEEKDAY(DATE($I$4,8,1))))+28)=8,DATE($I$4,8,1+((1-(2>=WEEKDAY(DATE($I$4,8,1))))*7)+(2-WEEKDAY(DATE($I$4,8,1))))+28,DATE($I$4,8,1+((1-(2>=WEEKDAY(DATE($I$4,8,1))))*7)+(2-WEEKDAY(DATE($I$4,8,1))))+21)
Last edited by mcinnes01; 06-10-2010 at 12:39 PM.
Both:
=DATE($I$4,6,1)-WEEKDAY(DATE($I$4,6,6))
and
=IF(MONTH(DATE($I$4,5,1+((1-(2>=WEEKDAY(DATE($I$4,5,1))))*7)+(2-WEEKDAY(DATE($I$4,5,1))))+28)=8,DATE($I$4,5,1+((1-(2>=WEEKDAY(DATE($I$4,5,1))))*7)+(2-WEEKDAY(DATE($I$4,5,1))))+28,DATE($I$4,5,1+((1-(2>=WEEKDAY(DATE($I$4,5,1))))*7)+(2-WEEKDAY(DATE($I$4,5,1))))+21)
return a value of 28/05/2012
for 2012, when the actual date should be 04/06/2012
can anyone help? this i think is my last date question, as long as nothing else goes wrong ....
This "does exactly what it says on the tin" - it give you the last Monday in May for the year in I4, even in 2012.Originally Posted by mcinnes01
My understanding is that 2012 is a "special case" because the Bank holiday will be moved by a week because of the Queen's Diamond Jubilee. You can't expect a formula to cater for that type of thing, I suggest you just have to make a manual adjustment for 2012.
Perhaps that backs up DonkeyOte's argument - it might be simpler to just list the holidays for each year rather than try to automate.
Ah that would explain why I couldn't get it work for that year. Thanks for you help, I just need to factor that static date in with a nice if statement and I will have a full list of working calendar formulas, I will post them all for peoples future reference.
Thanks again everyone!
Andy
Following a financial year so starting in april:
Easter:
=DATE(YEAR(I3),MONTH(DATE(YEAR(I3),MONTH(1),DAY(1)))+((INT((I2+(MOD((32+2*(MOD(INT(YEAR(I3)/100),4))+2*(INT((MOD(YEAR(I3),100))/4))-I2-(MOD((MOD(YEAR(I3),100)),4))),7))-7*(INT(((MOD(YEAR(I3),19))+11*I2+22*(MOD((32+2*(MOD(INT(YEAR(I3)/100),4))+2*(INT((MOD(YEAR(I3),100))/4))-I2-(MOD((MOD(YEAR(I3),100)),4))),7)))/451))+114)/31))-1),DAY(DATE(YEAR(I3),MONTH(1),DAY(1)))+(((MOD((I2+(MOD((32+2*(MOD(INT(YEAR(I3)/100),4))+2*(INT((MOD(YEAR(I3),100))/4))-I2-(MOD((MOD(YEAR(I3),100)),4))),7))-7*(INT(((MOD(YEAR(I3),19))+11*I2+22*(MOD((32+2*(MOD(INT(YEAR(I3)/100),4))+2*(INT((MOD(YEAR(I3),100))/4))-I2-(MOD((MOD(YEAR(I3),100)),4))),7)))/451))+114),31))+1)-1))
where i2 =
=(MOD((19*(MOD(YEAR(I3),19))+(INT(YEAR(I3)/100))-(INT(INT(YEAR(I3)/100)/4))-(INT(((INT(YEAR(I3)/100))-(INT(((INT(YEAR(I3)/100))+8)/25))+1)/3))+15),30))
and i3 = date
Good friday = -2 from above
Easter monday= +1 to easter sunday result
Early may bank holiday =
=DATE($I$4,5,1+7*1)-WEEKDAY(DATE($I$4,5,8-2))
Sping including queens jubilee adjustment =
=IF(I4=2012,DATE(2012,6,4),DATE($I$4,6,1)-WEEKDAY(DATE($I$4,6,6)))
Summer bank holiday =
=IF(MONTH(DATE($I$4,8,1+((1-(2>=WEEKDAY(DATE($I$4,8,1))))*7)+(2-WEEKDAY(DATE($I$4,8,1))))+28)=8,DATE($I$4,8,1+((1-(2>=WEEKDAY(DATE($I$4,8,1))))*7)+(2-WEEKDAY(DATE($I$4,8,1))))+28,DATE($I$4,8,1+((1-(2>=WEEKDAY(DATE($I$4,8,1))))*7)+(2-WEEKDAY(DATE($I$4,8,1))))+21)
Christmas day =
=IF(WEEKDAY(DATE($I$4,12,25))=7,DATE($I$4,12,25+2),IF(WEEKDAY(DATE($I$4,12,25))=1,DATE($I$4,12,25+1),DATE($I$4,12,25)))
Boxing day =
=IF(WEEKDAY(DATE($I$4,12,26))=7,DATE($I$4,12,26+2),IF(WEEKDAY(DATE($I$4,12,26))=1,DATE($I$4,12,26+2),IF(WEEKDAY(DATE($I$4,12,26))=2,DATE($I$4,12,26+1),DATE($I$4,12,26))))
New years day =
=IF(WEEKDAY(DATE($I$4+1,1,1))=7,DATE($I$4+1,1,1+2),IF(WEEKDAY(DATE($I$4+1,1,1))=1,DATE($I$4+1,1,1+1),DATE($I$4+1,1,1)))
i4 = year
Last edited by mcinnes01; 06-11-2010 at 04:39 AM.
Course you could get the August Bank holiday much more easily by using the same syntax as the late May holiday, i.e.
=DATE($I$4,9,1)-WEEKDAY(DATE($I$4,9,6))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks