I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay

Try this:

=DATE(2008,12,31)-WEEKDAY(DATE(2008,12,31))

or if you have a year in A1:

=DATE(A1,12,31)-WEEKDAY(DATE(A1,12,31))

Hope this helps.

Pete


On Mar 17, 2:42*pm, jlclyde wrote:
I am trying to find the last Saturday of the year with excel. *There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay

Put the year in A1 and then:

=DATE(A1+1,1,1)-WEEKDAY(DATE(A1+1,1,1))

For example:

2006 Saturday, December 30, 2006
2007 Saturday, December 29, 2007
2008 Saturday, December 27, 2008
2009 Saturday, December 26, 2009

--
Gary''s Student - gsnu200774


"jlclyde" wrote:

I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay

=DATE(YEAR(TODAY())+1,1,1)-WEEKDAY(DATE(YEAR(TODAY())+1,1,1))
--
David Biddulph

"jlclyde" wrote in message
...
I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay

On Mar 17, 10:06*am, Gary''s Student
wrote:
Put the year in A1 and then:

=DATE(A1+1,1,1)-WEEKDAY(DATE(A1+1,1,1))

For example:

2006 * *Saturday, December 30, 2006
2007 * *Saturday, December 29, 2007
2008 * *Saturday, December 27, 2008
2009 * *Saturday, December 26, 2009

--
Gary''s Student - gsnu200774



"jlclyde" wrote:
I am trying to find the last Saturday of the year with excel. *There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

neither of the formulas work correctly. They give a #NUM! Error. the
problem is that the year is not called out in the date so it is trying
to use the date in A1 as the year and not use the year of the value in
A1. So this woudl be the right formula.
=DATE(YEAR(A1)+1,1,1)-WEEKDAY(DATE(YEAR(A1)+6,1,1))

Thank you fir this help. I woudl not have got here with out both of
your replies.
Jay

On Mar 17, 10:36*am, jlclyde wrote:
On Mar 17, 10:06*am, Gary''s Student





wrote:
Put the year in A1 and then:

=DATE(A1+1,1,1)-WEEKDAY(DATE(A1+1,1,1))

For example:

2006 * *Saturday, December 30, 2006
2007 * *Saturday, December 29, 2007
2008 * *Saturday, December 27, 2008
2009 * *Saturday, December 26, 2009

--
Gary''s Student - gsnu200774

"jlclyde" wrote:
I am trying to find the last Saturday of the year with excel. *There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

neither of the formulas work correctly. *They give a #NUM! Error. *the
problem is that the year is not called out in the date so it is trying
to use the date in A1 as the year and not use the year of the value in
A1. *So this woudl be the right formula.
=DATE(YEAR(A1)+1,1,1)-WEEKDAY(DATE(YEAR(A1)+6,1,1))

Thank you fir this help. *I woudl not have got here with out both of
your replies.
Jay- Hide quoted text -

- Show quoted text -
I was wrong with my previous post. This formula does last day of the
last Saturday of the month and that is close enough. Thanks for the
help.

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

Jay

On Mar 17, 10:21*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=DATE(YEAR(TODAY())+1,1,1)-WEEKDAY(DATE(YEAR(TODAY())+1,1,1))
--
David Biddulph

"jlclyde" wrote in message

...



I am trying to find the last Saturday of the year with excel. *There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

David,
Yours goes from TODAY() and not from any year. It is close though. I
came up with this one to do last Saturday of the month. This will get
me close enough.

Thanks,
Jay
=DATE(YEAR(A1),MONTH(A1)+1,0)-
(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2),1)-5))

On Mar 17, 10:02*am, Pete_UK wrote:
Try this:

=DATE(2008,12,31)-WEEKDAY(DATE(2008,12,31))

or if you have a year in A1:

=DATE(A1,12,31)-WEEKDAY(DATE(A1,12,31))

Hope this helps.

Pete

On Mar 17, 2:42*pm, jlclyde wrote:



I am trying to find the last Saturday of the year with excel. *There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

Pete,

On Mar 17, 10:02*am, Pete_UK wrote:
Try this:

=DATE(2008,12,31)-WEEKDAY(DATE(2008,12,31))

or if you have a year in A1:

=DATE(A1,12,31)-WEEKDAY(DATE(A1,12,31))

Hope this helps.

Pete

On Mar 17, 2:42*pm, jlclyde wrote:



I am trying to find the last Saturday of the year with excel. *There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

Pete,
It works perfectly. I just had to add Year(A1) to go into the date.
Thanks,
Jay

Please read the replies, Jay, before saying "neither of the formulas work
correctly. They give a #NUM! Error."
Gary's reply said "Put the year in A1 ..."
Pete's said "or if you have a year in A1: ..."
Their formulae will give a #NUM! error if you put a *date* in A1, but not if
you put the *year* in A1.

Your formula doesn't give the #NUM! error if you put a date in A1, but your
formula returns a Sunday date for roughly 3 years out of 4, and the Saturday
for only one year out of 4.
--
David Biddulph

"jlclyde" wrote in message
...

neither of the formulas work correctly. They give a #NUM! Error. the
problem is that the year is not called out in the date so it is trying
to use the date in A1 as the year and not use the year of the value in
A1. So this woudl be the right formula.
=DATE(YEAR(A1)+1,1,1)-WEEKDAY(DATE(YEAR(A1)+6,1,1))

Thank you fir this help. I woudl not have got here with out both of
your replies.
Jay

On Mar 17, 10:06 am, Gary''s Student
wrote:
Put the year in A1 and then:

=DATE(A1+1,1,1)-WEEKDAY(DATE(A1+1,1,1))

For example:

2006 Saturday, December 30, 2006
2007 Saturday, December 29, 2007
2008 Saturday, December 27, 2008
2009 Saturday, December 26, 2009

--
Gary''s Student - gsnu200774



"jlclyde" wrote:
I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

If you want the last Saturday of the year, given a date in A1, all you need
to do is change the TODAY() in my formula to A1, in other words:
=DATE(YEAR(A1),1,1)-WEEKDAY(DATE(YEAR(A1),1,1))

I don't know what you think your formula below is doing, but it returns a
Saturday only once every 6 or 7 years. I'm glad you're happy with it!
--
David Biddulph

"jlclyde" wrote in message
...

David,
Yours goes from TODAY() and not from any year. It is close though. I
came up with this one to do last Saturday of the month. This will get
me close enough.

Thanks,
Jay
=DATE(YEAR(A1),MONTH(A1)+1,0)-
(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2),1)-5))

On Mar 17, 10:21 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=DATE(YEAR(TODAY())+1,1,1)-WEEKDAY(DATE(YEAR(TODAY())+1,1,1))
--
David Biddulph

"jlclyde" wrote in message

...



I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

Glad to see you got it working, with David's help.

Pete

On Mar 17, 3:54*pm, jlclyde wrote:

Pete,
It works perfectly. *I just had to add Year(A1) to go into the date.
Thanks,
Jay

On Mon, 17 Mar 2008 07:42:23 -0700 (PDT), jlclyde
wrote:

I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay

Last day of "this" year:

=DATE(YEAR(TODAY()),12,32)-WEEKDAY(DATE(YEAR(TODAY()),12,32))

Last day of year in A1 (where A1 contains the Year: e.g. 2007, 2008, etc)

=DATE(A1,12,32)-WEEKDAY(DATE(A1,12,32))

Last day of year in A1 where A1 contains a full date (e.g. 3/5/2008)

=DATE(YEAR(A1),12,32)-WEEKDAY(DATE(YEAR(A1),12,32))

--ron

On Mar 17, 11:08*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
If you want the last Saturday of the year, given a date in A1, all you need
to do is change the TODAY() in my formula to A1, in other words:
*=DATE(YEAR(A1),1,1)-WEEKDAY(DATE(YEAR(A1),1,1))

I don't know what you think your formula below is doing, but it returns a
Saturday only once every 6 or 7 years. *I'm glad you're happy with it!
--
David Biddulph

"jlclyde" wrote in message

...

David,
Yours goes from TODAY() and not from any year. *It is close though. *I
came up with this one to do last Saturday of the month. *This will get
me close enough.

Thanks,
Jay
=DATE(YEAR(A1),MONTH(A1)+1,0)-
(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2),1)-5))

On Mar 17, 10:21 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:



=DATE(YEAR(TODAY())+1,1,1)-WEEKDAY(DATE(YEAR(TODAY())+1,1,1))
--
David Biddulph

"jlclyde" wrote in message

...

I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

David,

On Mar 17, 11:08*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
If you want the last Saturday of the year, given a date in A1, all you need
to do is change the TODAY() in my formula to A1, in other words:
*=DATE(YEAR(A1),1,1)-WEEKDAY(DATE(YEAR(A1),1,1))

I don't know what you think your formula below is doing, but it returns a
Saturday only once every 6 or 7 years. *I'm glad you're happy with it!
--
David Biddulph

"jlclyde" wrote in message

...

David,
Yours goes from TODAY() and not from any year. *It is close though. *I
came up with this one to do last Saturday of the month. *This will get
me close enough.

Thanks,
Jay
=DATE(YEAR(A1),MONTH(A1)+1,0)-
(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2),1)-5))

On Mar 17, 10:21 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:



=DATE(YEAR(TODAY())+1,1,1)-WEEKDAY(DATE(YEAR(TODAY())+1,1,1))
--
David Biddulph

"jlclyde" wrote in message

...

I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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.
Jay

jlclyde wrote:

On Mar 17, 11:08 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:

If you want the last Saturday of the year, given a date in A1, all you need
to do is change the TODAY() in my formula to A1, in other words:
=DATE(YEAR(A1),1,1)-WEEKDAY(DATE(YEAR(A1),1,1))

I don't know what you think your formula below is doing, but it returns a
Saturday only once every 6 or 7 years. I'm glad you're happy with it!
--
David Biddulph

"jlclyde" wrote in message

...

David,
Yours goes from TODAY() and not from any year. It is close though. I
came up with this one to do last Saturday of the month. This will get
me close enough.

Thanks,
Jay
=DATE(YEAR(A1),MONTH(A1)+1,0)-
(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2),1)-5))

On Mar 17, 10:21 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:




=DATE(YEAR(TODAY())+1,1,1)-WEEKDAY(DATE(YEAR(TODAY())+1,1,1))
--
David Biddulph

"jlclyde" wrote in message

...

I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -


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.
Jay

You didn't remove the posts, they still propagated though the USENET
servers, they now exist in perpetuity.

On Mar 17, 4:02*pm, Bob I wrote:
jlclyde wrote:
On Mar 17, 11:08 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:

If you want the last Saturday of the year, given a date in A1, all you need
to do is change the TODAY() in my formula to A1, in other words:
=DATE(YEAR(A1),1,1)-WEEKDAY(DATE(YEAR(A1),1,1))

I don't know what you think your formula below is doing, but it returns a
Saturday only once every 6 or 7 years. *I'm glad you're happy with it!
--
David Biddulph

"jlclyde" wrote in message

....

David,
Yours goes from TODAY() and not from any year. *It is close though. *I
came up with this one to do last Saturday of the month. *This will get
me close enough.

Thanks,
Jay
=DATE(YEAR(A1),MONTH(A1)+1,0)-
(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2),1)-5))

On Mar 17, 10:21 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:

=DATE(YEAR(TODAY())+1,1,1)-WEEKDAY(DATE(YEAR(TODAY())+1,1,1))
--
David Biddulph

"jlclyde" wrote in message

....

I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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.
Jay

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

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

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

jlclyde wrote:

On Mar 17, 11:08 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:

If you want the last Saturday of the year, given a date in A1, all you need
to do is change the TODAY() in my formula to A1, in other words:
=DATE(YEAR(A1),1,1)-WEEKDAY(DATE(YEAR(A1),1,1))

I don't know what you think your formula below is doing, but it returns a
Saturday only once every 6 or 7 years. I'm glad you're happy with it!
--
David Biddulph

"jlclyde" wrote in message

...

David,
Yours goes from TODAY() and not from any year. It is close though. I
came up with this one to do last Saturday of the month. This will get
me close enough.

Thanks,
Jay
=DATE(YEAR(A1),MONTH(A1)+1,0)-
(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2), 1)-5))

On Mar 17, 10:21 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:

=DATE(YEAR(TODAY())+1,1,1)-WEEKDAY(DATE(YEAR(TODAY())+1,1,1))
--
David Biddulph

"jlclyde" wrote in message

...

I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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.
Jay

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

They may be removed from your computer, and so don't appear when you
look at the server you posted to. Perhaps swing over to google groups
and look at this newsgroup.

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

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
If you want the last Saturday of the year, given a date in A1, all you
need to do is change the TODAY() in my formula to A1, in other words:
=DATE(YEAR(A1),1,1)-WEEKDAY(DATE(YEAR(A1),1,1))

I don't know what you think your formula below is doing, but it returns a
Saturday only once every 6 or 7 years. I'm glad you're happy with it!
--
David Biddulph

"jlclyde" wrote in message
...

David,
Yours goes from TODAY() and not from any year. It is close though. I
came up with this one to do last Saturday of the month. This will get
me close enough.

Thanks,
Jay
=DATE(YEAR(A1),MONTH(A1)+1,0)-
(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2),1)-5))

On Mar 17, 10:21 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=DATE(YEAR(TODAY())+1,1,1)-WEEKDAY(DATE(YEAR(TODAY())+1,1,1))
--
David Biddulph

"jlclyde" wrote in message

...



I am trying to find the last Saturday of the year with excel. There
are a few sites that I visited, but they specify weekdays and I want
saturday.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

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

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 must have been half asleep when I did that, David - thanks for
pointing it out.

You start early !!

Pete

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