Comparing Weeks with today and selecting the right column

MRxlicius

New Member
Joined
Apr 5, 2018
Messages
21
Hi All,
I am trying to create a column that is basically, giving the variance of this week, to the previous week. I want to make this as automated as possible. An example is below: So in the table there are 5 columns with 5 dates, and I need in the 6th column to have O2-N2, and I need this to move as the weeks are passing by, so next week will show O2-P2, and so on. I want this variance to change, using the TODAY function, so when you open the file, to change.

NOPQRS
11/09/201918/09/201925/09/20192/10/20199/10/2019VS previous WEEK

<colgroup><col span="6"></colgroup><tbody>
</tbody>
Can you help please?
 

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)
I want this variance to change, using the TODAY function,

Can you explain how the date when you open the file may drive the formula to change ??
Is it in relation with the dates in the 5 columns ??
 
Last edited:
Upvote 0
So, basically, the date will change by using the function TODAY(). So the end result is always, current week vs past week. and the dates 11/09/2019 18/09/2019 25/09/2019 2/10/2019 will be changed manually every time there is an update needed, usually once every 3 months.
 
Last edited:
Upvote 0
Hi,
will there be data in your columns 9below the dates) and is it this you're trying to find the variance in.. not the dates?
 
Upvote 0
So the end result is always, current week vs past week.
In case the current week is not in the list of dates , like in your example we don't do anything , is it ?
The current week concerned dates starting at column "O" to column "R" !
For column "N" nothing to do !!
 
Last edited:
Upvote 0
Perhaps next code could help
Code:
Option Explicit


Sub Treat()
Dim I As Integer, II As Integer, LR As Integer
Dim MyWk As Integer
    MyWk = Application.WorksheetFunction.WeekNum(Now(), 2)
    LR = Cells(Rows.Count, "N").End(3).Row
    For I = 1 To LR
        If (IsDate(Cells(I, "N"))) Then
            For II = 1 To 4
                If (Application.WorksheetFunction.WeekNum(Cells(I, "N")(1, 1 + II), 2) = MyWk) Then
                    Cells(I, "N")(1, 6) = Cells(I, "N")(1, 1 + II) - Cells(I, "N")(1, II)
                    Exit For
                End If
            Next II
        End If
    Next I
'
End Sub
 
Upvote 0
Hi,
will there be data in your columns 9below the dates) and is it this you're trying to find the variance in.. not the dates?
Hi, yes there is going to be data. For example, week 1 is 100 next week is 50, so the variance should show -50, next week is also 50, so variance is 0. But i want the change in the formula to happen as automated as possible.
 
Upvote 0
Perhaps next code could help
Code:
Option Explicit


Sub Treat()
Dim I As Integer, II As Integer, LR As Integer
Dim MyWk As Integer
    MyWk = Application.WorksheetFunction.WeekNum(Now(), 2)
    LR = Cells(Rows.Count, "N").End(3).Row
    For I = 1 To LR
        If (IsDate(Cells(I, "N"))) Then
            For II = 1 To 4
                If (Application.WorksheetFunction.WeekNum(Cells(I, "N")(1, 1 + II), 2) = MyWk) Then
                    Cells(I, "N")(1, 6) = Cells(I, "N")(1, 1 + II) - Cells(I, "N")(1, II)
                    Exit For
                End If
            Next II
        End If
    Next I
'
End Sub

Hi,
I will give that a try, however i was trying to avoid vba since if there is an issue with the code, i know people will not be able to debug it, if i cannot do it.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top