Formula to show weeks from date range

Hi experts,

I have weekdays listed in column A from row 6 to 371 (this is fixed). Based on that range, how can I display on another sheet each week in column A? I'd like to avoid Week1, Week2, etc. I'd rather have the start date in one cell and the end date in another cell for each week (business days).

What would be the formula (VBA not an option)?

Thanks
STIWasabiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

etech0Commented:
Can you post your workbook?
0
STIWasabiAuthor Commented:
Attached but it's only a list of weekdays in column A of Sheet 1, nothing else. I want the week dates in Sheet 2.

Thanks
Book1.xlsx
0
NBVCCommented:
you can create a quick single column pivot table.

Make sure that you have a title for the column at the top.  Then go to Insert|Pivot Table.

Select the location and click Ok.

Now drag the Date column to Row labels area.

Right click the column in the Pivot table, select Group.

Make sure Start/End dates are correct, then Deselect Months, and Select Days.

At the bottom enter Number of Days: 5.

Click Ok.

Now you have a list of the weeks.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Steven CarnahanNetwork ManagerCommented:
Is this what you are after?  I am a little confused as sheet 1 has Dates not Days so I think you are asking backwards.
Copy-of-Book1.xlsx
0
Steven CarnahanNetwork ManagerCommented:
Or I added a third column with the week number of the the year here:
Copy-of-Book1.xlsx
0
barry houdiniCommented:
Sorry, perhaps I'm being a bit dense but exactly what do you expect the output to show on sheet2, do you want the Monday date and Friday date for all those dates?

Will all the dates be consecutive weekdays? If so try this

Put this formula in sheet2 A1

=MIN(Sheet1!A:A)-WEEKDAY(MIN(Sheet1!A:A),3)

and this one in A2 copied down as far down and further than you need

=IF(A1="","",IF(A1+7>MAX(Sheet1!A:A),"",A1+7))

and in B1 use this formula copied down

=IF(A1="","",A1+4)

If you add dates to the list then weeks get added automatically as long as you copied down the formulas far enough....otherwise they remain blank

see attached

regards, barry
weeks.xlsx
0
STIWasabiAuthor Commented:
Actually what I would is rather (sorry if my explanations were not clear) :
On Sheet 2 :
A1 9/30/2013 (first day of the week where Oct. 2nd falls)
B1 10/4/2013  (last day of the week where Oct. 2nd falls)
It would be the same until row 4 of Sheet1 is parsed because we are falling to the subsequent weeks (this would then be my second row on Sheet 2, the first line being the example shown above).

barryhoudini: just saw your comment, that does it except the dates will not always be consecutive, any tweaks should be made to the formulas?

Thanks
0
byundtMechanical EngineerCommented:
=Sheet1!A1-WEEKDAY(Sheet1!A1,3)           First Monday
=LOOKUP(A1+4,Sheet1!$A$1:$A$38)         First Friday

You may copy theMonday & Friday formulas down
0
barry houdiniCommented:
My solution above should do what you want - the weeks will automatically finish based on the dates you have in sheet1

regards, barry
0
Steven CarnahanNetwork ManagerCommented:
You could do it all on one column nicely with the following as well.

=TEXT(Sheet1!A1-(WEEKDAY(Sheet1!A1,2)-1),"dddd mmmm dd")&" to " &TEXT(TODAY(),"mmmm dd")
0
barry houdiniCommented:
OK, sorry I didn't see the point about the weeks maybe not being consecutive, just change A2 formula in Sheet2 to this version

=IFERROR(FLOOR(LARGE(Sheet1!A:A,COUNTIF(Sheet1!A:A,">"&B2)),7)+2,"")

see revised attachment where I altered some of the dates in sheet1

The week is included if any of the 5 days appears

regards, barry
weeks2.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
STIWasabiAuthor Commented:
Exactly what I needed, thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.