00:00Have you ever wondered how you can automatically populate date based on the changes to the month
00:04and year using a single formula in Excel like this? Well, the solution revolves around sequence
00:09function which needs the number of days in a month and the first day of the month. Let me
00:15demonstrate a few things here. We start off by using a date function to consolidate or reconstruct
00:21our date here. We're going to say date equal year comma and we're going to convert this month in
00:27text using a month function and then the argument with that in there will be the month in text
00:35and we're going to concatenate with zero and then the last one will be the day which is going to
00:41be
00:41the first of the month. When you hit enter you can see that it shows you the very first of
00:45December
00:462020. Now to get the very last day of the same month you basically use a function called EO month
00:54like this and EO month takes two arguments. The first one is going to be the date itself which is
01:01going to be that particular year and month and then comma zero. Zero in EO month function simply means
01:07that is the same month as the start date in this particular cell in D8 itself and you're going to
01:14close parenthesis and you hit enter. This will give you a number of days since January 1st 1900. Now to
01:20convert that into the day basically you encapsulate with day like this close parenthesis and hint enter
01:28and you get 31 days in the month of December. Now if you want to change this to 2024 and
01:35then maybe
01:35this changes to May you can see that it's got 31 days maybe get something a bit different. Here you
01:40go
01:40February 29th. Well February on 2024 there's 29 days. Now you can use this too just the number of rows
01:48which is
01:49going to be the number of days in the month and the starting day itself with the sequence function
01:53like this. The first one will be the rows which is the number of days comma we're not interested
01:59on columns and the start would be the first of that month close parenthesis and hit enter. It'll give
02:04you the array of dates for that particular selected year and month like this. I'm going to change this.
02:11You can see it changes nicely like that. Now they are quite messy like this. Now to consolidate them
02:18into one single formula what you do is that you're going to say equal. We're going to use a let
02:23function
02:25and for the very first one we're going to assign a variable a with the formula that we use to
02:31get
02:31the very first day of the month which was a date open parenthesis it's going to be this cell here
02:38year comma month and it's going to be this text as month concatenate with zero and then very first
02:48day of that particular month and year that this particular value here this one here get assigned
02:55to a variable a. Let's give it a new line here in a couple of spaces here. Now the third
03:03argument on
03:03the let we're going to use sequence function open parenthesis. The sequence function requires the
03:09very first argument as a number of rows which we streamline by using the day function and the
03:15e or month function. Basically it was e or month and e or month basically uses this formula the date
03:23output itself which is going to be a which we have assigned over here as a very first argument here
03:28and then comma zero which means the same month itself and basically what we did was that we convert
03:37that number of days since January 1st 1900 using a day function like this. The second argument we're not
03:45dealing with columns because it's going to leave it blank like this and then the start day was basically
03:51this one here which we have assigned as variable a. Close parenthesis for sequence and one more for the let
03:58and if you hit enter it gives you the list of days for March 2015 and as part of a
04:04cleanup we can basically
04:04clean this guy up and now just as a test you can change the month itself you can see that
04:10the date changes
04:12accordingly.
04:14you
04:14you
04:14you
04:14you
04:14you
04:14you
04:19You
