Solved

How to write a formula in Excel that will calculate week number?

Posted on 2011-09-11
225 Views
I've attached a sample workbook. Here is the breakdown.

Column C is the "Event Date" this is the date an event happened.
Column D is the "Followup Activity Date", These are the dates for all of the activity going forward for the "Event Date".
Column E or higher(not in the spreadsheet) would be data that relates to the "Followup Activity Date"

For example the "Event Date"  might be the day someone signed up for a class or even better  started a project. Column E might be what was accomplished and Column D would be the date it was accomplished.

In the workbook you will see two additional columns - A & B. In column A I calculated what number day it is for the entire date range for the Event Date in column C. This was not a problem. What I am stuck on is calculating the week number. The first 7 days will be week 1. The second seven days will be week 2 and so on until we reach the next Event Date which in this workbook would be 6/2/2011

What formula will work? Activity-Report.xlsx
0
Question by:futr_vision
• 2

LVL 50

Expert Comment

ID: 36520167
I don't see any attachment....

but if you have number of days in A2 the try this formula for week number in B2

=INT((A2+6)/7)

regards, barry
0

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 36520169
....OK, now I see the attachment!

The formula I suggested should work for you, put in B2 and copy down.....

regards, barry
0

Featured Post

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …