Date help - on each Jan and July 15 of each year

Experts, I need to modify the below for a date that lands on Jan 15 and July 15 of each year.  The below is for 60 days after and I need to make the modification to my new criteria.  I thought I could simply delete the +60 but that does not solve it.

I also need to use the PREVIOUS workday and not the next workday.
thank you.

=WORKDAY(DATE(YEAR(H207),CHOOSE(MONTH(H207),IF(DAY(H207)>15,7,1),7,7,7,7,7,IF(DAY(H207)>15,13,7),13,13,13,13,13),15+60)-1,1,Holidays_US_Jap)
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Director, Practice Manager and Computing ConsultantCommented:
Is this correct? You want Jan 15 and July 15, but the previous workday?

If so:

=WORKDAY(DATE(YEAR(H207),CHOOSE(MONTH(H207),IF(DAY(H207)>15,7,1),7,7,7,7,7,IF(DAY(H207)>15,13,7),13,13,13,13,13),15),-1,Holidays_US_Jap)
0
Project financeAuthor Commented:
Hi Philip,

Maybe it makes a difference if I am dragging the formula down?
I dont seem to get what I am after as the same date appears in each cell.

thank you
EE-Each-Jan-July15.xlsx
0
Director, Practice Manager and Computing ConsultantCommented:
What are you after?
0
Project financeAuthor Commented:
the display should be either Jan 15 or July 15 of that year taking into account the holiday tab and workday (not weekends).  You can see from the excel attached it is not.
0
Commented:
Insert in A2 and copy down this formula, to get the last workday before the next Jan 15 or July 15.
=WORKDAY(DATE(YEAR(A1),MONTH(A1)+6,15),-1,Holidays_US_Jap)
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Hello Ejgil, I was just posting something almost identical! I think the 15 should be a 16, though, so that you will actually get the 15th when it isn't a holiday or weekend

regards, barry
0
Project financeAuthor Commented:
Barry, I tried it Ejgil and your way and I confirm that the 15 should be a 16.  Please see attached
EE-Each-Jan-July15-Barry.xlsx
0
Project financeAuthor Commented:
i will wait any comments before awarding points.
0
Commented:
Points to Ejgil, please - it was his answer with only a small tweak from me

regards, barry
0
Project financeAuthor Commented:
amirable.

thank you both..
0
Commented:
I used 15 because the question said
need to use the PREVIOUS workday
,
for a date that lands on Jan 15 and July 15 of each year
Perhaps I misunderstood :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.