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

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

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

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

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

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 -

=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: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))

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 -

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.

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 -

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