pdvsa
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),C HOOSE(MONT H(H207),IF (DAY(H207) >15,7,1),7 ,7,7,7,7,I F(DAY(H207 )>15,13,7) ,13,13,13, 13,13),15+ 60)-1,1,Ho lidays_US_ Jap)
I also need to use the PREVIOUS workday and not the next workday.
thank you.
=WORKDAY(DATE(YEAR(H207),C
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
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
regards, barry
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
EE-Each-Jan-July15-Barry.xlsx
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
regards, barry
ASKER
amirable.
thank you both..
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 yearPerhaps I misunderstood :)
If so:
=WORKDAY(DATE(YEAR(H207),C