I am trying to calcuate which days are long holidays in a specific month. I was able to determine the holidays, and even have a formula I found online for calcualting the "Long Holiday" for the New Years, but can't figure out how to do it for other days in the month. To be clear, I am looking to input a specific month in one field, and then return the name of the holiday(s) that occur in that month/year. I then will add up the total number of holidays in that month. See attached.
Change the formula in E8 to =IF(OR(WEEKDAY(D8)=6,WEEKDAY(D8)=2),"Long weekend for ","No long weekend for ")&" "&$D$5 and copy it down.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thank you for your formula. It worked like a Champ. The second part of my challenge is to be able to input any given month and have an adjacent field auto calculate the number of holidays in that month based on this formula. See attached updated file referenced as:
Holiday Calcs v.2.
Thanks in advance!
e21=SUMPRODUCT(--(TEXT(D$8:D$18,"mmmyy")=TEXT(D21,"mmmyy")))
Try this and copy towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
This works great. The only problem is I need to manually change the date in cell D5 if the Month I am inputting falls on a different year. To further clarify, I am working on a project that needs to show what the number of holidays there are for 3 consecutive months. These months could fall within 2 separate years (i.e. December 2014, January 2015 and February 2015. Ideally I would like to input a month and have it auto calculate the number of holidays in that month. Is there a way to do this so I do not have to manually change cell D5?
By the way, I did find a pretty nice website that does what I am looking for, but I need to incorporate the results in my excel file, hence I prefer to build the formula.
http://www.timeanddate.com/date/workdays.html
Thanks
Last edited by Perk1961; 07-27-2015 at 01:10 AM.
By the way, I did find a pretty nice website that does what I am looking for, but I need to incorporate the results in my excel file, hence I prefer to build the formula.
http://www.timeanddate.com/date/workdays.html
Thanks
Is this what you meant?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Almost!! The issue I am having is that the project I am working on requires 3 unique fields based off of 3 unique Months. As such, while the new formula works perfectly for a single month, the moment I add that formula into a field with a different year it gives an incorrect answer. See attached to see what I mean. I am not sure how to get around it, short of adding a larger database table reference that is more inclusive of a greater date range.
Certainly D7 to D17 can't be valid for two years at the same time!! I can't think of a sensible way forward; other than to extend your database.
Thank you for all your effort. It really helped. I agree that the only solution is likely to extend the data base.
Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks