Forum Discussion
Dynamically Adjusting Remaining Values
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?
Thanks. See the attached demo workbook. Please note that the formulas in columns F, G etc. are different from those in column E.
7 Replies
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?
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 Could you provide one or two rows from both sheets with values filled in, showing the expected result?
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
