Link to home
Start Free TrialLog in
Avatar of pdvsa
pdvsaFlag for United States of America

asked on

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)
Avatar of Phillip Burton
Phillip Burton

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)
Avatar of pdvsa

ASKER

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
What are you after?
Avatar of pdvsa

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of pdvsa

ASKER

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
Avatar of pdvsa

ASKER

i will wait any comments before awarding points.
Points to Ejgil, please - it was his answer with only a small tweak from me

regards, barry
Avatar of pdvsa

ASKER

amirable.

thank you both..
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 :)