On Mar 18, 1:36*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Yes, even after correcting your formula to
=DATE(YEAR(A1),12,31)-WEEKDAY(DATE(YEAR(A1),12,31))*(WEEKDAY(DATE(YEAR(A 1),*12,31))<7)
[dealing with the misprint of YEAR(A1)A1],
I think for elegance your modified formula now gets beaten by Ron's
=DATE(YEAR(A1),12,32)-WEEKDAY(DATE(YEAR(A1),12,32))

or by my
=DATE(YEAR(A1)+1,1,1)-WEEKDAY(DATE(YEAR(A1)+1,1,1))
[which I'd posted in a wrong version until this morning, foolishly ignoring
my own frequent advice to copy directly rather than retype.] *:-(
--
David Biddulph

"Pete_UK" wrote in message

...
David is right. It was at the back of my mind that my formula might
not work when 31st Dec happens to be a Saturday, and that it would
return the previous Saturday, but when Jay said it worked I let it go.
You could get the correct result with this amendment:

=DATE(YEAR(A1),12,31)-
WEEKDAY(DATE(YEAR(A1)A1,12,31))*(WEEKDAY(DATE(YEAR (A1)A1,12,31))<7)

but it's not quite so elegant now.

Pete

On Mar 17, 9:41 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:



Yes, that's true, Jay. The reason for posting the corrections is not to
belittle you for getting things wrong (as none of us are immune from
errors), but to avoid confusing other readers who may either be reading
the
thread currently or be coming along later to read the group's archive.

You will have realised, I hope, that although you may regard Pete's as
"easily the most elegant of the solutions", it doesn't always give the
answer you requested. For years such as 2005, 2011, and 2016, you may wish
to look as to whether Pete's formula gives December 24th, not the 31st
which
I assume you wanted when you asked for the last Saturday of the year?
--
David Biddulph

"jlclyde" wrote in message

...
On Mar 17, 4:02 pm, Bob I wrote:

jlclyde wrote:
...
David,
I am not sure what I did to offend you, but the formulas did not
work. Also you are replying to posts that I removed, so I was aware
that it did not work. Pete's was easily the most elegant of the
possible soloutions.
You didn't remove the posts, they still propagated though the USENET
servers, they now exist in perpetuity.- Hide quoted text -

- Show quoted text -

They are not showing on my computer when I look at the site. So if I
screw up i need to post an additional time to let everyone know that I
ahve in deed screwed up?
Jay- Hide quoted text -

- Show quoted text -

David, I had noticed the problem with Petes after moving it to my
workbook. I amended it with a long If statement that said that if it
was not week 53 then add 7 days. What i am trying to do is find out
when our calendar starts over for vacation time. It starts the first
week of the year but sometimes(More often then not) the weeks start on
the previous year. You are correct, yours is the best and easiest to
use.

I want to thank all of you for all the time that you have spent with
this. Not only showing me this formula but also showing me prorper
posting etiquite.

Jay