Find Last Saturday of the Year

J

jlclyde

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
 
P

Pete_UK

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
 
G

Gary''s Student

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
 
J

jlclyde

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






- Show quoted text -
Click to expand...

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
 
J

jlclyde

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 -
Click to expand...
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
 
J

jlclyde

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







- Show quoted text -
Click to expand...

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))
 
J

jlclyde

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




- Show quoted text -
Click to expand...

Pete,
 
J

jlclyde

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




- Show quoted text -
Click to expand...

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

David Biddulph

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


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
 
D

David Biddulph

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


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))
 
R

Ron Rosenfeld

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
Click to expand...

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
 
J

jlclyde

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



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





- Show quoted text -
Click to expand...

David,
 
J

jlclyde

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



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





- Show quoted text -
Click to expand...

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
 
B

Bob I

jlclyde said:
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
Click to expand...

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

jlclyde

jlclyde said:
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

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

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 -
Click to expand...
Click to expand...
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
Click to expand...

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

- Show quoted text -
Click to expand...

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
 
D

David Biddulph

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: ....
Click to expand...
You didn't remove the posts, they still propagated though the USENET
servers, they now exist in perpetuity.- Hide quoted text -

- Show quoted text -
Click to expand...

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
 
B

Bob I

jlclyde said:
jlclyde said:
On Mar 17, 11:08 am, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:
Click to expand...
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))
Click to expand...
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!
Click to expand...
news:5ef6cdad-f1e7-472a-8f4c-73c1b59a8007@f63g2000hsf.googlegroups.com...
Click to expand...

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))
Click to expand...

On Mar 17, 10:21 am, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:
Click to expand...
=DATE(YEAR(TODAY())+1,1,1)-WEEKDAY(DATE(YEAR(TODAY())+1,1,1))
Click to expand...
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.
Click to expand...
Thanks,
Jay- Hide quoted text -
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
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
Click to expand...

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

- Show quoted text -
Click to expand...


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
Click to expand...

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

Pete_UK

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
 
Top