Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / General Excel Questions / March 2008

Tip: Looking for answers? Try searching our database.

Find Last Saturday of the Year

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jlclyde - 17 Mar 2008 15:42 GMT
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
Reply to this Message
Pete_UK - 17 Mar 2008 16:02 GMT
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

> 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
Reply to this Message
jlclyde - 17 Mar 2008 16:53 GMT
> Try this:
>
[quoted text clipped - 16 lines]
>
> - Show quoted text -

Pete,
Reply to this Message
jlclyde - 17 Mar 2008 16:54 GMT
> Try this:
>
[quoted text clipped - 16 lines]
>
> - Show quoted text -

Pete,
It works perfectly.  I just had to add Year(A1) to go into the date.
Thanks,
Jay
Reply to this Message
Pete_UK - 17 Mar 2008 17:39 GMT
Glad to see you got it working, with David's help.

Pete

> Pete,
> It works perfectly.  I just had to add Year(A1) to go into the date.
> Thanks,
> Jay
Reply to this Message
Gary''s Student - 17 Mar 2008 16:06 GMT
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

Signature

Gary''s Student - gsnu200774

> 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
Reply to this Message
jlclyde - 17 Mar 2008 16:36 GMT
On Mar 17, 10:06 am, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> Put the year in A1 and then:
>
[quoted text clipped - 18 lines]
>
> - 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
Reply to this Message
jlclyde - 17 Mar 2008 16:43 GMT
> On Mar 17, 10:06 am, Gary''s Student
>
[quoted text clipped - 33 lines]
>
> - 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
Reply to this Message
David Biddulph - 17 Mar 2008 16:59 GMT
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.
Signature

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

On Mar 17, 10:06 am, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> Put the year in A1 and then:
>
[quoted text clipped - 19 lines]
>
> - Show quoted text -
Reply to this Message
David Biddulph - 17 Mar 2008 16:21 GMT
=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
Reply to this Message
jlclyde - 17 Mar 2008 16:45 GMT
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))
> --
[quoted text clipped - 8 lines]
>
> - 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))
Reply to this Message
David Biddulph - 17 Mar 2008 17:08 GMT
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!
Signature

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))
> --
[quoted text clipped - 12 lines]
>
> - Show quoted text -
Reply to this Message
jlclyde - 17 Mar 2008 21:35 GMT
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:
[quoted text clipped - 36 lines]
>
> - Show quoted text -

David,
Reply to this Message
jlclyde - 17 Mar 2008 21:38 GMT
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:
[quoted text clipped - 36 lines]
>
> - 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
Reply to this Message
Bob I - 17 Mar 2008 22:02 GMT
> On Mar 17, 11:08 am, "David Biddulph" <groups [at] biddulph.org.uk>
> wrote:
[quoted text clipped - 46 lines]
> possible soloutions.
> Jay

You didn't remove the posts, they still propagated though the USENET
servers, they now exist in perpetuity.
Reply to this Message
jlclyde - 17 Mar 2008 22:13 GMT
> > On Mar 17, 11:08 am, "David Biddulph" <groups [at] biddulph.org.uk>
> > wrote:
[quoted text clipped - 55 lines]
>
> - 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
Reply to this Message
David Biddulph - 17 Mar 2008 22:41 GMT
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?
Signature

David Biddulph

On Mar 17, 4:02 pm, Bob I <bire...@yahoo.com> 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
Reply to this Message
Pete_UK - 18 Mar 2008 01:25 GMT
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
[quoted text clipped - 27 lines]
> ahve in deed screwed up?
> Jay
Reply to this Message
David Biddulph - 18 Mar 2008 07:36 GMT
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.]  :-(
Signature

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

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
[quoted text clipped - 31 lines]
> ahve in deed screwed up?
> Jay
Reply to this Message
Pete_UK - 18 Mar 2008 12:32 GMT
> 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
Reply to this Message
jlclyde - 18 Mar 2008 14:14 GMT
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(A1),­12,31))<7)
[quoted text clipped - 61 lines]
>
> - 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
Reply to this Message
Bob I - 17 Mar 2008 22:42 GMT
>>>On Mar 17, 11:08 am, "David Biddulph" <groups [at] biddulph.org.uk>
>>>wrote:
[quoted text clipped - 60 lines]
> 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.
Reply to this Message
David Biddulph - 18 Mar 2008 07:23 GMT
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.
Signature

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:
[quoted text clipped - 31 lines]
>>
>> - Show quoted text -
Reply to this Message
Ron Rosenfeld - 17 Mar 2008 17:41 GMT
>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
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.