Week Help


New Member
Sep 13, 2006
I have data which is broken up into days. I need this to be segregated into weeks. Is there an easy way to use VB or a macro to do this?

Here is a sample of what I am doing (I want to insert a column that says "week 1" after the first week, "week2" after the second, and so on). The weeks are designated sat-fri.

Tuesday Wednesday Thursday Saturday Monday Friday
100 100 100 100 100 100

So, I'd like it to say:
Tuesday Wednesday Thursday Week1 Saturday Monday Friday Week2
100 100 100 300 100 100 100 300

I have no idea how to do this without manually looking at all 730 columns. Any help would be greatly appreciated!!

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Since Excel only has 256 columns, and you speak of 730 columns, there seems to be some confusion here. Perhaps, after you clarify this, someone will be able to help you.
Upvote 0
Hmm, that would put me, with my Excel 97, a mere ten years in back of him! But, that would explain the difference!
Upvote 0
Assume then, that I have 20 columns of data and I need to insert "week#" in the manner described above. I am just looking for some VB help.
Upvote 0
Looks pretty awesome to me, and, without a doubt, will reuire a VBA guru. I am not that, so, I bow out!
Upvote 0
Hi there

I've pulled together a little sub routine that should do what you require. You need to insert the following code into a module in your VBA editor (Accessed by pressing Alt + F11, going to Insert > Module)

Option Base 1
Option Explicit
Dim week_array(7) As String
Const row_index As String = 1   'change this to reflect row number where days go across from

Sub splitweeks()
Dim col_index As Integer, wnum As Integer
col_index = 2                   'change this to reflect column number where *SECOND* day begins
week_array(1) = "Saturday": week_array(2) = "Sunday": week_array(3) = "Monday"
week_array(4) = "Tuesday": week_array(5) = "Wednesday": week_array(6) = "Thursday": week_array(7) = "Friday"
wnum = 1
'Cells(row_index, (col_index-1)) = "Week 1"
Do While Cells(row_index, col_index) <> ""
    If get_week_index(Cells(row_index, (col_index - 1))) > 1 And get_week_index(Cells(row_index, (col_index))) < get_week_index(Cells(row_index, (col_index - 1))) Then
            Cells(row_index, col_index).Select
            Cells(row_index, col_index) = "Week " & wnum
            wnum = wnum + 1
            col_index = col_index + 1
    End If
    col_index = col_index + 1
End Sub
Function get_week_index(day As String) As Integer
Dim x As Integer
x = 1
Do While x < (UBound(week_array) + 1)
    If week_array(x) = day Then
        get_week_index = x
        Exit Do
    End If
    x = x + 1
If x = UBound(week_array) + 1 Then
    get_week_index = -1
End If
End Function

You should be able to see from the comments where you can change either the row or column variables to reflect where the days of the week are in your spreadsheet. At the moment, it presumes they start from cell A1, but if you alter col_index and row_index to reflet where they start in your book it should work. Note, you should refer to the second day with col_index, as Week 1 is automatically inserted in A1 of the sheet as the first few days should belong to that week.

Hope this helps


**Edit** Sorry, just read your question again, and it seems you want week 1 to appear after the first week has been displayed. As such I have edited the code so that it doesn't start off with week 1 in the first cell, and instead waits until the first week has finished before it inputs 'Week 1' - we can change it back if you change your mind.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back