Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Populate Dates Automatically

0

Hi, just wondering if it's possible to have excel populate dates automatically between two given dates on a spreadsheet?

i.e. A1 has a start date and B1 has an end date which are months apart. Once the dates are inputted; then say from A2 down shows the dates inbetween, more so broken down into months rather than days.

Eventually all the dates will be shown on a chart of some sort.

excel
MS Office Version: 365
posted by: Jp_L (rep: 4) on Mon Mar 2, 2020 at 8:15 pm
Answer
Discuss

Answers

0
Selected Answer

With a start date in A1 and an end date in B1 the formula below could be in A2 and copied down from there as far as you want.

=IF(DATE(YEAR(A$1),MONTH(A$1)+ROW()-1,0)>DATE(YEAR(B$1),MONTH(B$1)+1,0),"",DATE(YEAR(A$1),MONTH(A$1)+ROW()-1,0))

In plain English: Show the last day of the month of A1.
Increase that date by one month for each row.
Until the date is later than the last day of the month in B1.
Thereafter insert "" instead of the date.

Obviously, the rules I have made about showing the last day of each month can be modified. What can't be changed is the need to have formulas in the cells below the end of the list. If that is an issue you will either need VBA or an array formula.

posted by: Variatus (rep: 4889) on Mon Mar 2, 2020 at 9:15 pm
Discuss

Discussion

Thanks Variatus, that worked nicely.
I'll attempt to do the same with numbers.
Jp_L (rep: 4) Mar 2, '20 at 9:43 pm
I can't get this to give me any number
=IF(DATE(YEAR(B$3),MONTH(B$3)+ROW()-1,0)>DATE(YEAR(B$4),MONTH(B$4)+1,0),"",DATE(YEAR(B$3),MONTH(B$3)+ROW()-1,0))

My start date is in B3 and end date is in B4.  Answer is a blank cell.

What am I doing wrong?  B3 and B4 are formatted as dates and the cell with the fomula is formatted as a number. 

If I could get this to work, could I get it to tell me end of the month day - start day in the first cell and start of the month + remaining days in the last cell?
jmccarty (rep: 2) Feb 25, '22 at 12:44 am
Go back to basics: Date([Year], [Month], [Day]) produces a date. Obviously, [Day] can't be smaller than 1 or bigger than 31. But Excel allows spill-over. If [Month] specifies February, with 28 days, then Date(2022,2,31) will return March 3. Similarly, Date(2022,2,0) will return Jan 31, which is a useful way to get the last date of any month.
Now, in your formula [Day] is specified as 0 in this way and [Month] as MONTH+ROW()-1. ROW() returns the row number where the formula resides, ROW()-1 the row above it. Therefore your formula will return a different date depending upon the row number where it resides.
I think your formula works just fine but it returns "" all the time because of the row number in which it is located. Pickout the dates from your formula and paste them to indiidual cells taking care of the rows where you paste. For example, paste =DATE(YEAR(B$3),MONTH(B$3)+ROW()-1,0) to F5, =DATE(YEAR(B$4),MONTH(B$4)+1,0) to G5 and  =DATE(YEAR(B$3),MONTH(B$3)+ROW()-1,0) to H5. Now you can see what's going on and you can write =F5>G5 in I5 to see how the dates are evaluated in your actual formula.
Variatus (rep: 4889) Feb 25, '22 at 3:33 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login