Dynamically Adjusting Remaining Values

[User9573053]

Hello,

I have an Excel workbook with two sheets: "Time Allocated" and "Time Spent". At the moment I have the same data in each sheet - in column A I have a list of unique project reference codes; in column B I have the total days allocated for each project; in column C I have the start month of each project (based on the first day of the month); in column D I have the end month of each project (based on the last day of the month). In columns E-W, I have months Jun-23 to Dec-24.

In the "Time Allocated" sheet, in each row against each project, I want a formula to automatically split the total days allocated between the relevant project months equally. For instance, in row 9 I have a total project budget of 11 days, a start month of Jun-23 and an end month of Oct-23, so 5 month duration - so I want the project budget split under the monthly columns as 2.2 in each month. I don't want to show any allocated time after Oct-23 for that particular project, but if I had a project with a start month of Nov-23 and an end month of Apr-24, I would only want the allocated time split equally between those months.

In the "Time Spent" sheet, I will be entering the time spent in each month under the same monthly columns. Back on the "Time Allocated" sheet, I then want the time available in each subsequent month for that project to reduce accordingly to show the remaining time available, but not to reduce the allocated time in preceding or the same month as the logged time. Continuing my above example, if I log 2.2 in Jun-23 (column E) then the remaining allocation doesn't need to adjust, but if I log 4 in Jul-23 (column F), I want the remaining months (Aug-23 to Oct-23, G-I) to reduce equally accordingly, so in this instance they would show 1.6 days available.

I want this formula to work for any project in my range, and any project duration between Jun-23 and Dec-24.

Can anyone help please?

[#2]

@User9573053 

Could you attach a small sample workbook with some dummy data, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

@HansVogelaar I can't share a workbook due to workplace settings, but here's my table below (apologies, will need pasting into Excel). At the moment, the table is duplicated on each sheet - Time Allocated would be the one with the formula, Time Spent would be where I manually enter the data.

Project Ref	Total Days	Project Month Start	Project Month End	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
1	19	Jun-23	Jun-23
2	6	Jun-23	Jun-23
3	4	Jun-23	May-23
4	4	Jun-23	Jul-23
5	7	Jun-23	Oct-23
6	2	Jun-23	Jun-23
7	2	Jun-23	Jun-23
8	11	Jun-23	Nov-23
9	16	Jun-23	Aug-23
10	6	Jun-23	Nov-23
11	10	Jun-23	Dec-23
12	12	Jun-23	Dec-23
13	12	Jun-23	Dec-23
14	1	Jun-23	Jun-23
15	4	Jun-23	Jun-23
16	1	Jun-23	Jun-23
17	1	Jun-23	Jun-23
18	1	Jun-23	Jun-23
19	0.5	Jun-23	Jun-23
20	3	Jun-23	Jun-23
21	2	Jun-23	Jun-23
22	10	Jun-23	Jun-23
23	1	Jun-23	Jun-23
24	3	Jun-23	Jun-23
25	1	Jun-23	Nov-23
26	0	Jun-23	Dec-23
27	0	Jun-23	Dec-23
28	0	Jun-23	Dec-23
29	12	Jun-23	Dec-23
30	12	Jun-23	Dec-23
31	1	Jun-23	Jun-23
32	3.5	Jun-23	Sep-23
33	1	Jun-23	Jun-23
34	0	Jun-23	Aug-23
35	0.5	Jun-23	Jun-23
36	8	Jun-23	Jun-23
37	2.5	Jun-23	Jun-23
38	2	Jun-23	Jun-23
39	1	Jun-23	Jun-23
40	2	Jun-23	Jun-23
41	22.5	Jun-23	Jun-23
42	4.5	Jun-23	Sep-23
43	3	Jun-23	Sep-23

[#4]

I can't share the workbook due to workplace settings sorry but here's the data, the same in both sheets - Time Allocated would be where I need the formula, Time Spent would be where I manually input the data.

Project Ref	Total Days	Project Month Start	Project Month End	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
1	19	Jun-23	Jun-23
2	6	Jun-23	Jun-23
3	4	Jun-23	May-23
4	4	Jun-23	Jul-23
5	7	Jun-23	Oct-23
6	2	Jun-23	Jun-23
7	2	Jun-23	Jun-23
8	11	Jun-23	Nov-23
9	16	Jun-23	Aug-23
10	6	Jun-23	Nov-23
11	10	Jun-23	Dec-23
12	12	Jun-23	Dec-23
13	12	Jun-23	Dec-23
14	1	Jun-23	Jun-23
15	4	Jun-23	Jun-23
16	1	Jun-23	Jun-23
17	1	Jun-23	Jun-23
18	1	Jun-23	Jun-23
19	0.5	Jun-23	Jun-23
20	3	Jun-23	Jun-23
21	2	Jun-23	Jun-23
22	10	Jun-23	Jun-23
23	1	Jun-23	Jun-23
24	3	Jun-23	Jun-23
25	1	Jun-23	Nov-23
26	0	Jun-23	Dec-23
27	0	Jun-23	Dec-23
28	0	Jun-23	Dec-23
29	12	Jun-23	Dec-23
30	12	Jun-23	Dec-23
31	1	Jun-23	Jun-23
32	3.5	Jun-23	Sep-23
33	1	Jun-23	Jun-23
34	0	Jun-23	Aug-23
35	0.5	Jun-23	Jun-23
36	8	Jun-23	Jun-23
37	2.5	Jun-23	Jun-23
38	2	Jun-23	Jun-23
39	1	Jun-23	Jun-23
40	2	Jun-23	Jun-23
41	22.5	Jun-23	Jun-23
42	4.5	Jun-23	Sep-23
43	3	Jun-23	Sep-23

[#5]

@User9573053 

Could you provide one or two rows from both sheets with values filled in, showing the expected result?

[#6]

@HansVogelaar 

Sure thing! So on "Time Allocated", before anything is entered into "Time Spent" the allocations for Projects 4 & 8 should show like this (I've included less rows for ease):

Project Ref

Total Days

Project Month Start

Project Month End

Jun-23

Jul-23

Aug-23

Sep-23

Oct-23

Nov-23

Dec-23

Jan-24

Feb-24

Mar-24

Apr-24

May-24

Jun-24

Jul-24

Aug-24

Sep-24

Oct-24

Nov-24

Dec-24

1

19

Jun-23

Jun-23

2

6

Jun-23

Jun-23

3

4

Jun-23

May-23

4

4

Jun-23

Jul-23

2

2

5

7

Jun-23

Oct-23

6

2

Jun-23

Jun-23

7

2

Jun-23

Jun-23

8

11

Jun-23

Oct-23

2.2

2.2

2.2

2.2

2.2

9

16

Jun-23

Aug-23

Then on "Time Spent", if I enter this data:

Project Ref

Total Days

Project Month Start

Project Month End

Jun-23

Jul-23

Aug-23

Sep-23

Oct-23

Nov-23

Dec-23

Jan-24

Feb-24

Mar-24

Apr-24

May-24

Jun-24

Jul-24

Aug-24

Sep-24

Oct-24

Nov-24

Dec-24

1

19

Jun-23

Jun-23

2

6

Jun-23

Jun-23

3

4

Jun-23

May-23

4

4

Jun-23

Jul-23

2

5

7

Jun-23

Oct-23

6

2

Jun-23

Jun-23

7

2

Jun-23

Jun-23

8

11

Jun-23

Nov-23

2.2

9

16

Jun-23

Aug-23

There shouldn't be any change to the data in "Time Allocated" as the correct allocated time was used, so future month allocations remain the same. But, if I enter the data below into "Time Spent":

Project Ref

Total Days

Project Month Start

Project Month End

Jun-23

Jul-23

Aug-23

Sep-23

Oct-23

Nov-23

Dec-23

Jan-24

Feb-24

Mar-24

Apr-24

May-24

Jun-24

Jul-24

Aug-24

Sep-24

Oct-24

Nov-24

Dec-24

1

19

Jun-23

Jun-23

2

6

Jun-23

Jun-23

3

4

Jun-23

May-23

4

4

Jun-23

Jul-23

1

5

7

Jun-23

Oct-23

6

2

Jun-23

Jun-23

7

2

Jun-23

Jun-23

8

11

Jun-23

Nov-23

2.2

4

9

16

Jun-23

Aug-23

I've gone under the allocation on Project 4 in June, and as-expected on Project 8 in June, but then I've gone over in July. I would then want the data in "Time Allocated" to dynamically update to:

Project Ref

Total Days

Project Month Start

Project Month End

Jun-23

Jul-23

Aug-23

Sep-23

Oct-23

Nov-23

Dec-23

Jan-24

Feb-24

Mar-24

Apr-24

May-24

Jun-24

Jul-24

Aug-24

Sep-24

Oct-24

Nov-24

Dec-24

1

19

Jun-23

Jun-23

2

6

Jun-23

Jun-23

3

4

Jun-23

May-23

4

4

Jun-23

Jul-23

1

3

5

7

Jun-23

Oct-23

6

2

Jun-23

Jun-23

7

2

Jun-23

Jun-23

8

11

Jun-23

Oct-23

2.2

4

1.6

1.6

1.6

9

16

Jun-23

Aug-23

This has then decreased the allocation for Project 4 in June and increased the remaining allocation for July. On Project 8, it's kept June's allocation the same as it was, increased July's and subsequently reduced the available allocation for August-October. 

Hope that makes sense, thanks Hans 🙂

[#7 · Accepted solution]

@User9573053 

Thanks. See the attached demo workbook. Please note that the formulas in columns F, G etc. are different from those in column E.

[#8]

I think this is it, thank you so much Hans! I'll do some more testing 🙂