How to Autofill Dates in Excel Based on Another Cell | Easy Formula Guide

[00:00:00]okay so you want to auto fill dates in Excel based on another cell we're going to look at several calculations here you might want to add days months years or working days to your start date first of all I'm going to show you how to do this with the sequence function if you don't have the sequence function I will later on in the video show you how to do this with other functions that you will have so let's start off by returning days based on the start date just a heads up all my dates are in US format so for this I'm going to use the sequence function now the first argument is rows so I'm going to arrange my dates in this column and I need to specify how many rows in this column I'm going to fill with dates so the value for that is in here in C2 number of days five days I don't need more than one column so I just skip over that argument and my start value is in here C3 so I close the bracket and press enter now this will return the date serial numbers if you want to convert them to actual dates you need to select the sales and then up here on the Home tab change the format of those cells to one of the date formats and if I change this number to 10 it would return 10 Dates now if you want to return a series of dates as column headings you can also use the sequence function for that equals sequence rows I would ignore because I only want one row so just a comma then columns that'll be five columns referenced up here in C2 and my start date would be this start date here close the bracket press enter and again I need to format the cells and if I change this to say seven dates it would return seven dates the sequence function okay so that's how you return days based on a date in another sale what if you want to return months or you can do this with the e-date function has two arguments start date which we can reference here and then months so if months contain the value 2 it would return a date two months on from my start date I want to return the months value using the sequence function so I want 10 dates or however many dates I put in F2 I don't need any more columns and my start number would be zero because the first state I want to return is the start date Zero months on from the start date so I close the bracket twice press enter it returns all these serial numbers I select the sales convert them to dates or format them as dates and you can see that that is working so change the number of months to five it would return five months okay so if you want to auto fill dates a year apart this is what you would do you would use the date function and it has three arguments a year month and day now what we need to do is extract the year portion of our start date the month portion of it and the day portion of it so to extract the year portion I would use the year function year i3 and I need to increase that year as the Formula Auto fills down so I can use the sequence function to do that rows well the number of dates I want to return is specified here and don't need any more columns and the start number would be zero so the first year needs to be the same as the start date year comma and then month I need to extract the Month part of this date and I can use the month function for that and then day we can use the day function to extract the day portion of this date press enter and I'll need to format all these cells with the date format and if I change the number of years to five it would return five dates one year apart okay what if you want to auto fill working days based on the date in another cell and to do this you would use the workday function so I'm assuming here that working days on Monday through to Friday so my start date would be here and the number of days that I want to move on from that start date I can calculate using the sequence function so I want 10 Dates don't need any more columns my start number is going to be zero because I want to return the start date in the first instance so close the bracket there close the bracket there press enter and if I format these dates now I'm going to format these dates in a particular way so it shows the day of the week in fact let's select a few more sales the cell selected I'm going to use the shortcut key control 1 and in this dialog box I'm going to go to the custom category I'm going to delete General and in this type box I'm going to type 4ds space d space 4ms space three wise click on OK and that will show the day of the week as a word so you can see I'm just getting Mondays through to Fridays now the other thing you can do here is specify that certain Monday through to Friday dates are holidays so I've listed my holidays here and I can use the last argument here the holidays argument to specify those holiday dates so now you'll see it skipped over the 10th and the 13th of March okay what if you're working days weren't Monday to Friday say they were Monday to Wednesday well you can do this using workday International so my start date would be here in Q3 days I'm returning using the sequence function so rows specified up here don't need any more columns start would be zero close the bracket comma Now weekends now you can select the pattern of non-working time here so for example if you're non-working days or Monday and Tuesday you could just select this option if you're only non-working day was Sunday you could select 11. we want to say that only Monday Tuesday and Wednesday are working days now to do this start with the speech mark and then I'm going to put a zero in for a working day and a one in for a non-working day and it starts on Monday so Monday is a working day Tuesday's a working day Wednesday is a working day Thursday is non-working so it's Friday Saturday and Sunday so I close that with the speech mark close the bracket press enter I'm going to format these dates using the same format that I've applied here so I'm going to select a cell that contains the format go to the format painter and then copy that format to these cells so if I change the number of days I want to return to 10 you can still see that it's just returning Mondays through to Wednesdays so if you have the sequence function this is the best way to autofill dates based on another cell now if you don't have the sequence function this is what you would do now without the sequence function you can specify your start date and then add the result of the row function now the row function Returns the row number of the set address that you specified so if I click in A1 row number for A1 is one so that returns one day on from our start date now we actually want to return the start date so I need to say -1 at the end Now using this method I am going to have to copy the formula down we didn't have to do that with the sequence function so because of that I need to lock the reference to C3 so I click somewhere in that cell reference and then I'm pressing f4 on my keyboard and that'll put the dollars in that cell reference which means it's a locked cell reference as we copy the formula down it's always going to refer to that cell address if F4 doesn't work for you try function F4 if that doesn't work just type the dollars in so if I copy this down you can see it increments the date now if you need to be able to specify the number of dates that are autofilled you need to put this within the IF function so we'd say if row A1 is less than or equal to the value I have in C2 which I also need to lock comma and my value of true would be this formula otherwise I want an empty text string so two speech marks nothing in between close the bracket press enter and now if I copy this down it'll only show four dates if I change this to 10 it will show 10 Dates now if you needed to arrange your dates of across a row so it's column headings you would use the column function so equals this start date Plus column and for column what it's going to do is return the column position of the cell reference that you specify so a1's column position is one so if I press enter you can see it returns one Dayton from this date now I do want to return that date so all I do is I say minus one because I need to copy this across I need to lock the reference to C3 so now if I copy that across you can see increments the dates now if I wanted to respond to the number of days I input there I use if again logical tests would be column A1 less than or equal to this value here which I need to lock if true turn the result of this formula otherwise return an empty texturing so if I copy this across then I can change this number and it will return the correct number of dates now for months reduce edit so my start date would be here the number of months I want to return I've used the row function form so row A1 minus one because the first day I want to return is the start date close the bracket press enter now because I'll be copying this formula down I do need to lock my reference to F3 if I copy this down format it with the short date format you can see it auto fills the month element of the date now if I wanted to respond to the number of months I put up here I use that if function trick so if row A1 is less than or equal to the number of dates I specified up here and I need to lock that reference then return the result of the e-date function otherwise return an empty text room copy that down and if I change this to three I'd only get three dates and for the Year One we would use the date function so first of all I need to specify the year of the date I want to return so I'd have to extract the year of the start date I can do that using the year function and I would need to add X number of years to that date so for that I can use the row function again row A1 and for the first date I want to return the current year so that'd be minus one then I would need to extract the month portion of this date so that's using the month function and then the day portion of our start date so I can use the day function for that now because I want to copy this down I need to lock my reference to i3 and then copy it down so all these dates are one year apart if I want the number of dates to correspond to whatever number I put in here I use the IF function trick so row A1 is less than or equal to the number of dates I'm specifying here lock that reference that's true return the result of this date function otherwise return an empty text string we're putting three here I get three dates working days this you'd use the workday function start date would be my start date here now I'm assuming Monday to Fridays are the working days days argument well I returned that using the row function again so row A1 first day I want to return is the current date so -1 and I close the bracket press enter now because I need to copy this down I need to lock my reference to L3 then I can copy it down and I'm going to apply a custom format here so it shows the day of the week so with those cells selected Ctrl 1 on my keyboard and go down to custom this type box I'm going to type 4ds space 1D Space 4 M's space three y's so you can see I'm only getting Monday through to Fridays now if I want to exclude holiday dates I can use the last argument within the workday function which is holidays so comma and then select those holiday dates and need to lock the reference to those holiday dates and if I copy that down you'll see it skips over the 10th and the 13th of March now if you want to specify non-standard non-working days for example we're going to say that our non-working days are Thursday through to Sunday you can use the workday International function so my start date would be Q3 the number of days I'm returning with the row function row A1 the first day I want to return is the current start date so that's -1 weekends now you can see in this list you can easily specify for example that Monday and Tuesday are your non-working days or Sunday only is your non-working day but in our example we want to specify that Thursday through to Sunday on non-working days so there's nothing in this list that supports our particular need so what you can do in speech mark is put a series of zeros and ones zeros or working days and ones or non-working days and we start with Monday so Monday is a zero it's a working day so it's Tuesday so it's Wednesday and Thursday Friday Saturday and Sunday on non-working days if we close the bracket press enter so I do need to lock my reference to Q3 before I copy down copy this down and I'm going to borrow the format that I applied over here so select one of those cells click on the format painter and copy the format over these dates you can see all the dates on Mondays through to Wednesdays and if I want it to be dynamic so it responds to whatever number of days I put in here I just use that if function trick if row A1 is less than or equal to the number I input in Q2 then return the result of the workday International function otherwise return an empty text string and I do need to lock my reference to Q2 before I copy down but now if I change this to three I'll only get three dates okay that's all I wanted to cover in this particular video you can download the file with all these different formulas but if you found it useful please give me a thumbs up and subscribe and I'll see you next video foreign foreign foreign foreign