You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Autofill Date help

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?

Posted on Jun 24, 2020 7:54 PM

Reply
Question marked as Top-ranking reply

Posted on Jun 25, 2020 12:53 AM

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

7 replies
Question marked as Top-ranking reply

Jun 25, 2020 12:53 AM in response to Reid6887

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

Jul 2, 2020 2:26 AM in response to Barry

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...


Jun 26, 2020 3:42 AM in response to Barry

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


Jun 26, 2020 10:35 PM in response to Reid6887

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

Autofill Date help

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.