How to hold value inside a cell like a timestamp

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This spreadsheet is designed to keep track of the number of mistakes made on important paperwork during the course of the week and for the month made by one of the employees in the list.
A brief description of the layout of the spreadsheet. In range A3:A51 is a list of all the employees for that work area and in range(B1:F1) contains the dates for the current week. In range (B2:F2) has the days of the week.
In range "H1" I used the below function to display the week number for that month.
In column G, sums the total number of errors made for the week for each employee.
In columns H through K displays the number of errors made for that week of the month.
Column H is for the first week of the month, Column I is for the second week of the month,
Column J is for the third week of the month,
Column K is for the fourth week of the month,
Inside each of those cells contains this formula:
Starting in:
Range H3 has this code:

=IF(H1="1",G3,0)
Range I3 has this code:
=IF(H1="2",G3,0)
Range J3 has this code:
=IF(H1="3",G3,0)
Range K3 has this code:
=IF(H1="4",G3,0)

So using the above row location "3" here is an example:
If Employee A located in row 3 has a total of 5 errors for week 1 located in range G3. Range H3 would hold the value of 5. If in week 2 they had a total of 2 errors for week located in range G3. Range I3 would hold the value of 2 and so on.

I found the function below that displays the week number for the month.
Code:
Function WeekOfMonth(dDate1 As Date) As String
    Dim dDate2 As String
    Dim wWeek As Integer
     
     'dDate2 is changed to date from String
     
    dDate2 = VBA.CDate(Month(dDate1) & "/01/" & Year(dDate1))
    wWeek = DateDiff("ww", dDate2, dDate1, vbSunday, vbUseSystem) + 1
     
     'Return the Week number of the month
     'Monday is taken as week starting date, you can change
     'it to your desired day as starting date of week
         
    WeekOfMonth = wWeek
    'WeekOfMonth = "Week " & wWeek
End Function
The problem I am having is if I change the date on my computer to a later week date or previous week and rerun the code the value that held the first value is now a zero and the week corresponding to the number in H1 holds the new value. So I guess what I am asking is if there is a way to similarly to timestamp that value to that cell so when the week number changes the previous value is still there. I know this explanation probably sounds very confusing so my apologizes for that. Please ask as many questions as necessary and thank you all for your help.

Thank you again.
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
IMO your underlying problem is down to the design of your file, and I strongly suggest you change your approach

In effect you're trying to make it look nice first, and then thinking about functionality - learn from databases: create an Excel-friendly structured data-entry section, that is then used to feed your various reports. It's easier to add a nice view to a well-modelled file, than to add functionality to a poorly-structured pretty file

I would have the following input table which has as many rows as you need it to, you just keep adding to it. 100 rows, 1000 rows, whatever...
column A) employee name
column B) date / time error made
columns c+) any other info you want to track (error type, cause, etc)

Now get all your reports to use this data directly using e.g. SUMIFS; there's no size limit (e.g. 5 columns), you can review everything easily over any date range which aids long-term trend analysis, and also resolves the date issue you're currently having. You don't have to calculate the week of month for each data item, that's a separate thing

This type of approach also helps disconnect your data from your modelling, which helps simplify version updates going forward - it becomes easier to port your data between file versions, or to swap different data sets into one model

HTH
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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