Every April 1 and Oct 1 of each year

Experts, The below formula gives me each Jan and July 15 of each year and I now need April 1 and Oct 1 of each year.  How to modify?  thank you in advance.

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+6,16),-1,Holidays_US_Jap)
pdvsaProject financeAsked:
Who is Participating?
 
barry houdiniCommented:
That formula will give you Jan 15 and Jul 15 if those are workdays, otherwise it gives you the previous working day - if you want the same setup but for April 1 and Oct 1 then, of course, the previous working day might be in March or September, is that OK?

That also complicates matters because the date might not be in the expected month. This formula should work regardless of the distribution of holidays, assumes A1 contains the first April 1 or Oct 1 with formula in A2 copied down

=WORKDAY(EOMONTH(WORKDAY(A1,1,Holidays_US_Jap),5)+2,-1,Holidays_US_Jap)

regards, barry
0
 
pdvsaProject financeAuthor Commented:
Perfect.  Preceding is OK for me.  thank you much....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.