CODEWITHSUNDEEP
excelexcel-formula
Steve K
Steve K

Reputation: 387

How can I find and show all Saturdays in a year but show 1st of each month if it falls in the week in Excel?

This one is driving me up a wall, so I hope somebody can help. Here's what I'd like to do.

I'd like dynamically show the Saturdays in each month, starting from 1/1/2014. If at any point, the first of the month occurs between the previous Sunday and the Saturday following it, I'd like to show the first of that month and then the end of the week. I have one exception to that, which is 1/1. Here are some examples. Take 1/1/2014:

The previous Sunday to 1/1/2014 is 12/29/2013. The following Saturday is 1/4, and 1/1 falls in between that week. However, I'd like to begin with the first Saturday in January, since it's the first week in the year. So, here's how January would look:

1/4/2014 1/11/2014 1/18/2014 1/25/2014

Now, since the next Saturday falls in a new month, I'd like to show the end of January and have the next cell be the following Saturday in February, like so:

1/4/2014 1/11/2014 1/18/2014 1/25/2014 1/31/2014 2/1/2014

Here's February into March, which is similar:

2/1/2014 2/8/2014 2/15/2014 2/22/2014 2/28/2014 3/1/2014

These are kind of consistent in that the next Saturday following the end of these two months happens to be the first of the next month. March into April is different, though. Consider:

3/1/2014 3/8/2014 3/15/2014 3/22/2014 3/29/2014 3/31/2014 4/5/2014

Here, the end of March falls in between the two months...

I hope this is clear because it's making my brain hurt.

Thanks for reading!

Upvotes: 0

Views: 5195

Answers (2)

ARich
ARich

Reputation: 3279

When I first read this, I thought you were asking about a VBA example... I now realize you weren't. However, I had already written a little something in VBA to accomplish this, so I've decided to post anyway. See below if you're interested.

Option Explicit

Sub ListDates()

Dim dt As Date
Dim sDate As Date
Dim eDate As Date
Dim i As Long

sDate = #1/1/2014#
eDate = #12/31/2014#
i = 1

'Loop through the dates.
For dt = sDate To eDate
    'If the date is at the end of the month, print to cell.
    If Month(dt) <> Month(DateAdd("d", 1, dt)) Then Cells(i, 1) = dt: i = i + 1
    'If the date is a Saturday, print to cell.
    If WeekdayName(Weekday(dt)) = "Saturday" Then Cells(i, 1) = dt: i = i + 1
Next dt

End Sub

Upvotes: 0

barry houdini
barry houdini

Reputation: 46331

If you want one long list of dates including all Saturdays and end of month dates in the year try this:

Put the year in A1, e.g. 2014 then in A3 use this formula for the first Saturday of the year

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

Then in A4 you can use this formula to get every successive Saturday or end of month date

=MIN(A3+8-WEEKDAY(A3+1),EOMONTH(A3+1,0))

copy that down as far as required

Upvotes: 3

Related Questions