I am trying to find a formula to auto populate an entire workbook for work days excluding major holidays. It the set up with each month as a page in the workbook. I need it to fill in the dates "01/01/2017" and day of the week "Mon" in the respective column. I also want it to fill the label with the month and year at the top of each page.
The laziest route is probably to search for Excel templates online to see if you can find one you like. Several different calendar and ledger templates are available for free, and it's possible that one might suit your purpose. If you want to make your own, you can enter 1/2/2017 (Jan. 1st was a Sunday) into A1, then use the formula below in A2:
=IF(WEEKDAY($A1+1,16)>2,$A1+1,IF(WEEKDAY($A1+2,16)>2,$A1+2,$A1+3))
Fill down to get all of the weekdays. In B1, use the following formula, then fill down to get the "Mon", "Tues", etc. alongside each date:
=TEXT($A1,"ddd")
You can repeat the process for each month, but my recommendation would be to do the entire year on one sheet. Next, copy A1:B365, paste values (ONLY) over the formulas, then just cut and paste each month to its own sheet. Having replaced the formulas with values, you could also just delete the holidays you want to remove. Hope this helps!
If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".
If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
https://www.excelforum.com/the-water...ke-thread.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks