Auto deleting a date column once the date has passed via VBA

[RMH2024]

Hello,

My practice uses excel spreadsheets to track resourcing requirements on a week to week basis, this is done for an 18 month period. The week commencing date starts at Column C with the date in row 1. Column B notes employees names, each row has one employee name. The total hours per employee sit in column c in the relevant week date next to their name.

Can you help provide a code that deletes the week commencing date when the following week starts? Then I’d need (if possible) a code that adds a week to the end. I want the excel sheet to have a constant rolling 18 month period.

Thanks.

 

[HighAndWilder]

Hello,

My practice uses excel spreadsheets to track resourcing requirements on a week to week basis, this is done for an 18 month period. The week commencing date starts at Column C with the date in row 1. Column B notes employees names, each row has one employee name. The total hours per employee sit in column c in the relevant week date next to their name.

Can you help provide a code that deletes the week commencing date when the following week starts? Then I’d need (if possible) a code that adds a week to the end. I want the excel sheet to have a constant rolling 18 month period.

Thanks.

Welcome to Mr Excel.

Are you able to post your data using XL2BB?

It will help people in coming up with the most suitable solution.

https://www.mrexcel.com/board/help/xl2bb/

 

[RMH2024]

Welcome to Mr Excel.

Are you able to post your data using XL2BB?

It will help people in coming up with the most suitable solution.

https://www.mrexcel.com/board/help/xl2bb/

 

[RMH2024]

Team Resourcing.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		Staff Member	18-Nov	25-Nov	02-Dec	09-Dec	16-Dec	23-Dec	30-Dec	06-Jan	13-Jan	20-Jan	27-Jan	03-Feb	10-Feb	17-Feb	24-Feb	03-Mar	10-Mar	17-Mar	24-Mar	31-Mar	07-Apr	14-Apr	21-Apr	28-Apr
2	Weekly Total Hours
3		RH	17	14	21	21	35	35	35	14	14	14	14	4	4	4	4	0	0	0	0	0	35	35	0	0
4		DM	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5		JH	7	7	21	21	35	35	35	17	17	10	10	0	0	0	0	0	0	0	0	0	0	0	0	0
6			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
8			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9	Annual Leave
10		RH	7	7	7	7	35	21	35														35	35
11		DM
12		JH	7	7	7		35	35	35
13
14
15
16	Time on other Assoc jobs
17		RH					H																H	H
18		DM
19		JH			10	21	H	H		17	17	10	10
20
21
22
23	Practice Management & Ad Hoc
24		RH	10				H	14															H	H
25		DM
26		JH			4		H	H
27
28
29
30	Project
31		Work Stage	Stage 3 - 140 Hours
32	Total Target Hours:	RH		7	14	14	H			14	14	14	14	4	4	4	4						H	H
33		DM
34	Hours to Date:	JH					H	H
35
36
37	Hours Remaining:
38	-107	Total	0	7	14	14	0	0	0	14	14	14	14	4	4	4	4	0	0	0	0	0	0	0	0	0
Resource Planner

Cell Formulas
Range	Formula
C3	C3	=SumColoredCells($B$3,C10:C914)
D3:Z3	D3	=SumColoredCells($B$3,D10:D914)
C4	C4	=SumColoredCells($B$4,C10:C914)
D4:Z4	D4	=SumColoredCells($B$4,D10:D914)
C5:Z5	C5	=SumColoredCells($B$5,C10:C914)
C6:Z6	C6	=SumColoredCells($B$6,C10:C914)
C7:Z7	C7	=SumColoredCells($B$7,C10:C914)
C8:Z8	C8	=SumColoredCells($B$8,C10:C914)
A38	A38	=A33-(A35+SUM(D38:Z38))
C38:Z38	C38	=SUM(C31:C37)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A38	Cell Value	<0	text	NO
A38	Cell Value	>0	text	NO
C7:Z7	Cell Value	=35	text	NO
C7:Z7	Cell Value	>35	text	NO
C3:Z6,C8:Z8	Cell Value	=35	text	NO
C3:Z6,C8:Z8	Cell Value	>35	text	NO

 

[RMH2024]

Welcome to Mr Excel.

Are you able to post your data using XL2BB?

It will help people in coming up with the most suitable solution.

https://www.mrexcel.com/board/help/xl2bb/

Thats me uploaded that. Hope you can help!