Pookiemeister
Well-known Member
- Joined
- Jan 6, 2012
- Messages
- 626
- Office Version
- 365
- 2010
- Platform
- 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.
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.
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
Thank you again.
Last edited: