Date of last Friday in a month

[ByteMe]

Hi all,

Is it possible with a formula to find the date of the last friday of a given month?

E.g. The last friday date of July 2006 would be 28/07/06

Any help greatly appreciated.

Regards

 

[plettieri]

Hi:

You might try this

=EOMONTH(A1,1)-WEEKDAY(EOMONTH(A1,1)+3,3)


with any date in a1.

Taken from a earlier post.


hope this helps

plettieri

 

[NBVC]

Try:

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

Where A1 houses full date, including month of interest.

 

[ByteMe]

Thanks to you both

Although the first post doesn't seem to work on my version of Excel, the second post from NBVC works perfectly.

Any chance I can be cheeky and ask you to explain or breakdown the formula for me? I'd like to understand exactly what it does.

Kind regards

 

[barry houdini]

You can calculate the previous Friday from a date in A1 with

=A1-WEEKDAY(A1+2)+1

This works because when A1 is a Friday =WEEKDAY(A1+2) is 1, when A1 is a Saturday =WEEKDAY(A1+2) is 2, etc.

If you want to calculate the last Friday in a month you can substitute A1 with a formula that calculates the last date of the month, e.g.

=EOMONTH(A1,0)

or

=DATE(YEAR(A1),MONTH(A1)+1,0)

which would give you either

= EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)+2)+1

which is not the same as above but correct, I think. EOMONTH requires Analysis ToolPak to be installed

or

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

which was what NBVC suggested

 

[NBVC]

The first part of the formula finds the first day of the following month and converts it to an Excel serial number (Excel uses serial numbers to refer to dates, these numbers can be manipulated with arithmetic functions). The second part does the same thing for the 2nd day of the following month, but goes further on to convert it to a weekday number (Sunday being 1 and Saturday 7).

Let's say your date in A1 is July.

The function first evaluates the date serial number for Aug 1.

DATE(YEAR(A1),MONTH(A1)+1,1) converts to DATE(2006,07+1,1) Which evalutes to DATE(2006,08,1) which finally converts to serial number, 38930.

The second part evaluate to one additional day within the Weekday() function, therefore WEEKDAY(38931) and August 2nd is a Wednesday, so the Weekday() function evaluates to 4. Subtracting the correct amount is the key and so subtracting the weekday number for whatever the second day of the following month is, will yield the previous Friday (if you subtract the weekday number for the 3rd day of the following month, you'll get the previous Thursday, and so on....)

So, finally the formula subtracts 4 from the Aug 1st serial number, 38930 to get 38926, which formatted as a date gives the last Friday of the month.

If you use Tools|Formula Auditing|Evaluate Formula and step through, you will see how Excel evaluates the formula.

EDIT: Well, I see Barry came to the rescue... I am sure his explanation is easier to figure out. But I will leave mine as I went through the effort of writing it. Let us know if you have further questions.

EDIT ii: Just noticed I had Saturday 6, in my 1st sentence, should have been Saturday 7 (as it is now))

 

[ByteMe]

Thank you so much - very much appreciated.

Kind regards