no analysis toolpak

[Emjaye]

Hi all.

I found the perfect solution to my problem - identifying the last Sunday of a month - by using the formula

INT((EOMONTH(L1,0)-1)/7)*7+1

making use of the analysis toolpak

Our inglorious I.T dept did an upgrade this week and took the add-in's out of excel and are unlikely to put them back because

" it's not in our remit "

however I could always make a business case to have them installed (aaaaaaaaaaarrrrrrrrrrrrrgh!)

Now, past experience has shown that getting a business case passed has about as much chance as you and me seeing seahorses flying broomsticks to Mars, therefore I am looking for an alternative method, that does not need an add-in

please

 

[Ron Coderre]

Certainly not as elegant as the EOMONTH version...
but, try this regular formula:

=L1-DAY(L1)+MAX(DAY(L1+1-WEEKDAY(L1+1-7,2)+7*{1;2;3;4;5}))

Does that help?

 

[VoG]

Or another

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

 

[Emjaye]

thanks guys, they both work brilliantly.

 

[T. Valko]

Certainly not as elegant as the EOMONTH version...
but, try this regular formula:

=L1-DAY(L1)+MAX(DAY(L1+1-WEEKDAY(L1+1-7,2)+7*{1;2;3;4;5}))

Does that help?

If the date in L1 is >= the date of the last Sunday then that formula fails.

 

[barry houdini]

You could perhaps fix that with this version along the same lines.....

=L1-DAY(L1)+MAX(DAY(L1-DAY(L1)+1-WEEKDAY(L1-DAY(L1))+7*{4;5}))

I note that the original formula with EOMONTH doesn't work if you are using 1904 date system......but assuming that's not an issue then this version will also work

=FLOOR(L1-DAY(L1)+31-DAY(L1-DAY(L1)+32),7)+1