Forum Discussion
Help to generate a simple table
Hi all
I am looking for some assistance please.
My team will manually input a start date and a duration.
This will show an end date. Simple enough!
What i would then like is a table create extracting this info.
So if the Start Date is Mar 25 - Duration 12 Months
The table would automatically be created below showing Mar 25, April 25, May 25, etc in independent cells.
I will then take a value for a different place and spread this against the relevant months
Thanks!
Final question - hopefully just as straightforward. Is there then a way to determine a certain % of the overall value being attributed. So at the moment the formular evenly distributes the value over the total months. What if i wanted to say the first month is always £50k, the 2nd month is always £100k, the third is always £200k, the last month is always £100k and then split the remaining value between the remaining duration
See the attached demo. It will only work correctly if the duration is more than 3 months.
This is perfect - Thank you so much
I now have a further query if you dont mind! So in another cell i will have a total Value. I would now like to split this value by the number of months relative to the dates.
So using the above - £120,000 is a total sum.
I would like this to be divided by the duration months and it will appear below the months that have been created above.
That way i have a monthly cost table created.
Thank you
With Start Date in B1, Duration in B2 and Total Amount in B3, enter the following formula in another cell:
=EDATE(B1, SEQUENCE(, B2, 0))
And in the cell below it:
=IF(SEQUENCE(, B2), B3/B2)
You can even combine them in one formula:
=VSTACK(EDATE(B1, SEQUENCE(, B2, 0)), IF(SEQUENCE(, B2), B3/B2))
This is perfect! Thank you.
Let's say Start Date is in B1 and Duration in B2.
In another cell, enter the following formula if you want the dates in a column:
=EDATE(B1, SEQUENCE(B2, , 0))
If you want the dates in a row:
=EDATE(B1, SEQUENCE(, B2, 0))
The result will spill to as many cells as needed. Format them as dates.
