Find Last Saturday of the Year

[David Biddulph]

As I said elsewhere in the thread, we all make mistakes. In changing the
formula when you said you didn't want TODAY(), the +1 got missed.
It should, of course, have been
=DATE(YEAR(A1)+1,1,1)-WEEKDAY(DATE(YEAR(A1)+1,1,1))
Apologies for the confusion.

 

[David Biddulph]

Yes, even after correcting your formula to
=DATE(YEAR(A1),12,31)-WEEKDAY(DATE(YEAR(A1),12,31))*(WEEKDAY(DATE(YEAR(A1),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

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

 

[Pete_UK]

Yes, even after correcting your formula to
=DATE(YEAR(A1),12,31)-WEEKDAY(DATE(YEAR(A1),12,31))*(WEEKDAY(DATE(YEAR(A1),­12,31))<7)
[dealing with the misprint of YEAR(A1)A1],

I must have been half asleep when I did that, David - thanks for
pointing it out.

You start early !!

Pete

 

[jlclyde]

Yes, even after correcting your formula to
=DATE(YEAR(A1),12,31)-WEEKDAY(DATE(YEAR(A1),12,31))*(WEEKDAY(DATE(YEAR(A1),­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


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

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?

"jlclyde" <[email protected]> wrote in message

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