Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

Every Oct 8

experts, i need a formula for each oct 8 of each year.  t should be draggable down several rows from start date in A1. if oct 8 is a weekend then i need preceding workday.

thank you
0
pdvsa
Asked:
pdvsa
1 Solution
 
Wilder1626Commented:
HI

Would you be able to provide an excel template that we can work with?

This should help us to understand what you really need.
0
 
pdvsaAuthor Commented:
Hi, I am on my phone at the moment.  

Let's say cell a1 is October 8, 2015.  I need out 8 of each succeeding year and using previous workday if lands on a weekend.  

Thank you
I hope my explanation is clear.
0
 
Saqib Husain, SyedEngineerCommented:
Try this formula in A2 and drag down

=DATE(YEAR(A1)+1,10,8-MAX(0,WEEKDAY(DATE(YEAR(A1)+1,10,8),2)-5))
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pdvsaAuthor Commented:
Saqib, that works.  

Would you happen to know how I could incorporate a holidays named range?  I have a tab with a listing of holidays and if the date lands on the holiday then use the preceding workday.
0
 
Saqib Husain, SyedEngineerCommented:
Unless you have oct 6th or oct 7th as a holiday you probably do not have to worry about holidays.
0
 
barry houdiniCommented:
...a bit late for this one, I know.......but if you use WORKDAY function you can exclude holidays if that might be a factor, i.e. using this formula in A2 copied down

=WORKDAY(DATE(YEAR(A1)+1,10,8)+1,-1,holidays)

regards, barry
0
 
pdvsaAuthor Commented:
Thank you Barry.  I will test that when I return to my computer.  Appreciate your response after knowing that the question was closed.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now