Thank you in advance for taking a look at this project. I am proficient in normal excel functions but am fairly new to writing VBA code and am completely lost with how to start with this project. I have tried several approaches all to no avail and now have no choice but to ask for help.
My end goal is to create a separate sheet with records that displays data extracted from another sheet.
The code will need to essentially record the start date/time of a down-shift and sum up the hours of consecutive down shifts where a shift is 8 hours.
Below is the format of the data that needs to be extracted. "X"s and "W"s represent up-shifts whereas "H" and blank cells represent down-shifts. Additionally "1/2" means half-shift. Every column represents a day and the rows represent the three shifts for each day.
Starting in cell E5, the code needs to iterate through the full range of each table by first going down three rows and then over to the next column to the right, etc.; all the way to the end of the table which is column "IV".
After that it will need to start over again for the next table, E12. There are multiple tables on the sheet that the code will have to iterate through in order to complete the project. These tables all share the same columns, are all formatted the same and are the same dimensions.
The results need to be in the below format on a newly created sheet in the same workbook.
Each record shows the department (column A in the original sheet), date/time that the down shift started and for how long (hours) it was down for.
Things to note:
- Dates are in the rows right above each table. They are formatted to only show the day.
- The start time for the first row in each table is from the day before. For example: the down shift that starts on cell F5 actually starts at 11:00 PM on the 20th.
- Column C is start time of shift and Column D is the end time.
- There are other shift indicators such as "R", "J", "1/4" and "3/4". I would like to be able to add new/revise existing indicators and their values to the code.
- For partial shifts (ie "1/2"), the downtime always start at the beginning of the shift.
Thank you again for your help and time!
My end goal is to create a separate sheet with records that displays data extracted from another sheet.
The code will need to essentially record the start date/time of a down-shift and sum up the hours of consecutive down shifts where a shift is 8 hours.
Below is the format of the data that needs to be extracted. "X"s and "W"s represent up-shifts whereas "H" and blank cells represent down-shifts. Additionally "1/2" means half-shift. Every column represents a day and the rows represent the three shifts for each day.
Starting in cell E5, the code needs to iterate through the full range of each table by first going down three rows and then over to the next column to the right, etc.; all the way to the end of the table which is column "IV".
After that it will need to start over again for the next table, E12. There are multiple tables on the sheet that the code will have to iterate through in order to complete the project. These tables all share the same columns, are all formatted the same and are the same dimensions.
The results need to be in the below format on a newly created sheet in the same workbook.
Each record shows the department (column A in the original sheet), date/time that the down shift started and for how long (hours) it was down for.
Things to note:
- Dates are in the rows right above each table. They are formatted to only show the day.
- The start time for the first row in each table is from the day before. For example: the down shift that starts on cell F5 actually starts at 11:00 PM on the 20th.
- Column C is start time of shift and Column D is the end time.
- There are other shift indicators such as "R", "J", "1/4" and "3/4". I would like to be able to add new/revise existing indicators and their values to the code.
- For partial shifts (ie "1/2"), the downtime always start at the beginning of the shift.
Thank you again for your help and time!
