We help IT Professionals succeed at work.

Every April 1 and Oct 1 of each year

pdvsa
pdvsa asked
on
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)
Comment
Watch Question

Most Valuable Expert 2013
Commented:
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
pdvsaProject finance

Author

Commented:
Perfect.  Preceding is OK for me.  thank you much....