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)
pdvsaProject financeAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
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
 
Phillip BurtonDirector, 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
 
pdvsaProject 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.  

Please see attached.

thank you
EE-Each-Jan-July15.xlsx
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
What are you after?
0
 
pdvsaProject 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
 
barry houdiniCommented:
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
 
pdvsaProject 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
 
pdvsaProject financeAuthor Commented:
i will wait any comments before awarding points.
0
 
barry houdiniCommented:
Points to Ejgil, please - it was his answer with only a small tweak from me

regards, barry
0
 
pdvsaProject financeAuthor Commented:
amirable.

thank you both..
0
 
Ejgil HedegaardCommented:
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
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.