Creating an amortization schedule and I’m trying to auto fill the dates to reflect the first of each month, and it’s auto filling to each date of the month instead (ie 7/1 turning into 7/2, etc) is there a way to make it full to the 1st of each new month?
Hi Reid,
Assuming the date are in column A and the first date is entered in A2:
A2: Enter the date in the format you wish to use.
A3: Enter this formula:
EDATE(A2,1)
Fill down for as many rows as needed.
If you want to fix the dates (remove the formula so that the dates stay the same), Select and copy column A, then go to the edit menu and choose Paste formula results. This action replaces all the formulas with the last date calculated by each,
Regards,
Barry
Hi Reid,
Assuming the date are in column A and the first date is entered in A2:
A2: Enter the date in the format you wish to use.
A3: Enter this formula:
EDATE(A2,1)
Fill down for as many rows as needed.
If you want to fix the dates (remove the formula so that the dates stay the same), Select and copy column A, then go to the edit menu and choose Paste formula results. This action replaces all the formulas with the last date calculated by each,
Regards,
Barry
Hi Reid,
One way is to use the DATE(Year, Month, Day) function.
Formula in B2 =DATE(2020,$A2,1)
Fill the formula down, select and copy column B and Menu > Edit > Paste Formula Results.
Then you can delete column A.
Regards,
Ian.
Barry,
sorry for the delay in responding....Hard to find time to actually sit down and focus on working on this, as there is so much wrong here! I decided to start a new sheet from scratch so as to try and fix each error one step at a time. The first problem that I see is that column A, the “Payment Due Date“ column, presents an error starting with the January 2021 payment, and the error is “DATE month argument should be between 1 and 12.” All of the cells in column A on this table are referencing E3, E4, and E5 from Table 1 where E3 is ‘2020’ E4 is ‘7’ or July and E5 is ‘1’...which are all referencing the Loan Start Date ‘B13’ of July 1, 2020. All of the lines for 2020 payments are not presenting an error, so I’m not sure what the error is trying to tell me? I did not modify this column in any way, the template came built like this, and it only came with 38 rows filled in from the start. Hopefully this screen shot shows enough for you to see everything in play here...
Barry,
Thank you for your help as always with my weird questions. I have one more problem that I’m running into with this amortization schedule. I created my file using one of the already built templates, but I’m having to customize it due to the fact that the interest rate on the loan is calculated as the interest rate divided by 365, then multiplied by the number of days in each month (ie January is charged 31 days, February 28 days, April 30 days, etc). I’m sitting here manually correcting each line to fix each month. Is there a way that the formula can reference the associated month on each corresponding line and adjust itself, or am I going to have to make manual adjustments for 360 lines?
an example of the formula for August looks like this: IPMT(Table 1::$B$5×31,Payment Number 'Aug 1, 2020',Table 1::$B$3,Table 1::$B$9,Table 1::$B$2,Table 1::$B$4)
B5 = periodic rate
B3 = number of payments
B9 = loan amount
B2 = end balance
B4 = when due
Hi Reid,
Weird questions are the most fun!
"the formula for August looks like this: IPMT(Table 1::$B$5×31,Payment Number 'Aug 1, 2020',Table 1::$B$3,Table 1::$B$9,Table 1::$B$2,Table 1::$B$4)"
Could you provide a coy of this formula as it would appear for the October 1, 2020 payment, please.
With August and its preceding month both having 31 days, the 31 in the formula above is ambiguous.
Also, I'd like to see what other values must be edited for each month's copy.
Regards,
Barry
Also, here is a screenshot of what the formula looks like for the 1/1/21 payment in the cell A8
Interesting question. Since it's different, could you start a new thread?
SG
Autofill Date help
