Date in middle of month

[Sunnyskies]

Morning,

Scenario. Have got an opening date say the 2006-10-01 now I want another
date - closing date the 2006-10-15 but if this closing date falls into a
weekend then I want the next working day to be shown. The closing date must
always be around the 15th of the month, but not on weekends (if so it can
then be on the Monday or preceeding Friday) as mentioned above.

My formula for closing date is currently =EOMONTH(C6,0) with is showing
2006-10-31, how can I modify this to meet my requirement mentioned above.

Thanks

 

[Jon von der Heyden]

Hi,

Try: =IF(WEEKDAY(B3,2)=7,B3+1,IF(WEEKDAY(B3,2)=6,B3-1,B3))
Where B3 houses the date.

 

[Sunnyskies]

Sorry Jon does not work, returns the same date as my opening date.

 

[Roger Govier]

Hi

with start date in A1
=A1+14+CHOOSE(WEEKDAY(A1+14,2),0,0,0,0,0,-1,1)
will give the weekday that is closest to the 15th of the month.

 

[Jon von der Heyden]

Is your date a formatted as a date?

 

[Jon von der Heyden]

Rogers formula uses exactly same logic as formula I gave you so expect same
issue will apply (but I think it's a slick way of doing it).

Suspect your date isn't formatted as date.

 

[Sunnyskies]

Yup, format-date-type-*2001-03-14

Is your date a formatted as a date?

 

[Roger Govier]

Hi Jon

Your posting assumed that the OP had already added the offset to the
date.
from their response

returns the same date as my opening date

I assumed that the date in the source cell was the 1st of the month.

Adding +14 to each occurrence of B3 in your formula, would produce the
correct result.

 

[Bob Phillips]

Another way

=workday(A1+13,1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Roger Govier]

Hi Bob

That's fine apart from April and July where the result is the 17th,
which is further away from the 15th than the Friday (14th).

As the OP said it could be the Friday before the weekend, I took it to
mean he wanted the date which was the closest to the 15th.

 

[Bob Phillips]

Okay, if you are going to make me work for it

=workday(A1+14,(WEEKDAY(A1)=1)-(WEEKDAY(A1)=7))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)