Last Friday of the month

[Alfredo_CPA]

I have in cell "A5" the last day of the month (i.e. las month was 08-31-08
and this month will be 09-30-08)
I need a formula in "A6" (that works for every month) that gives me the last
friday of that specific month based on the input on "A5"

Thanks

 

[Mike H]

Try

=DATE(YEAR(A1),MONTH(A1)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-2,-7)

With a date in a1

Mike

 

[T. Valko]

Try this:

=A5+1-WEEKDAY(DATE(YEAR(A$5),MONTH(A$5)+1,1)+1)

 

[Peo Sjoblom]

=DATE(YEAR(A5),MONTH(A5)+1,1)- WEEKDAY(DATE(YEAR(A5),MONTH(A5)+1,2))

--


Regards,


Peo Sjoblom

 

[Alfredo_CPA]

It does the job perfectly

Thanks a lot!!

Try

=DATE(YEAR(A1),MONTH(A1)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-2,-7)

With a date in a1

Mike

 

[Alfredo_CPA]

Thanks Mr T.
Of all the responses this is the shortest fromula and I personally prefer
that.

Thanks to you Peo as well (all the responses works perfectly)

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[T. Valko]

=A5+1-WEEKDAY(DATE(YEAR(A5),MONTH(A5)+1,1)+1)

We can reduce that further to:

=A5+1-WEEKDAY(A5+2)

 

[Bernd P]

Hi Biff,

What about
=A5-MOD(A5+1,7)
?

Regards,
Bernd

 

[T. Valko]

Even better.

I don't think we'll get it any more compact than that!