Automatically shift columns based on start/end date

[rflee97]

Hello everyone,

I am working on a project employee capacity-sheet, with several projects and the capacity for each month.
I want the percentages (D2:M4) shifted automatically when I, let's say, change the end date to 2023-05.

How do I achieve this? Thank you so much in advance!

Map1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			2022-05	2022-06	2022-07	2022-08	2022-09	2022-10	2022-11	2022-12	2023-01	2023-02	2023-03	2023-04	2023-05
2	Project A	Person A		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
3		Person B		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
4		Person C		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
5
6		Start date	2022-06
7		End date	2023-03
Blad1

 

[Coding4Fun]

Hello,

Your question seems to be lacking detail. You state that you want the percentages (D2:M4) to shift automatically but its not clear how exactly you would like things shifted.

Using the example you gave in this case changing the End Date in C7 from 2023-03 to 2023-05 you want the percentages to "shift"

Does this mean you then want the percentages to shift from D2:M4 over to F2:O4

Or are you then trying to have it populate N2:O4 with 100% so you then have D2:O4 set to 100%

Maybe include a before and after example of what you are trying to achieve, and what should it do for the blanks? In this example if you extend the End Date to 2023-05 there are no values in Columns N and O.

Please clarify what you are trying to achieve.

 

[kevin9999]

Is this what you're looking for?

Before

shift.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			2022-05	2022-06	2022-07	2022-08	2022-09	2022-10	2022-11	2022-12	2023-01	2023-02	2023-03	2023-04	2023-05
2	Project A	Person A		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
3		Person B		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
4		Person C		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
5
6		Start date	2022-06
7		End date	2023-03
Sheet1

Cell Formulas
Range	Formula
C2:O4	C2	=IF(AND(C$1>=$C$6,C$1<=$C$7),1,"")

After

shift.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			2022-05	2022-06	2022-07	2022-08	2022-09	2022-10	2022-11	2022-12	2023-01	2023-02	2023-03	2023-04	2023-05
2	Project A	Person A		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
3		Person B		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
4		Person C		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
5
6		Start date	2022-06
7		End date	2023-05
Sheet1

Cell Formulas
Range	Formula
C2:O4	C2	=IF(AND(C$1>=$C$6,C$1<=$C$7),1,"")