Sometimes excel interprets date formats as a text string. Microsoft Excel has an inbuilt date formula that any frequent user of excel is aware of. It is the primary function used to calculate dates in Excel, and it can be used to extract a date from a text string. The date function in Excel is the Date and time function that returns a serial number in sequence format representing a valid date. This function's beauty helps assemble dates that need to change dynamically based on other values in a data sheet. The formula is:
=DATE (year, month, day) where the arguments mean;
Year- It is a required argument of the function representing the number of the year of the Date. It is always expressed in four digits to avoid confusion.
Month- this is a required argument representing the number for the month of the year to use. It can be a + or – number which means the months from January (1) to December (12)
Day-it is also a required argument in Excel representing the number for the day or Date in a particular month.
It is a straightforward and more effortless function to use. Let's get to know how to use an Excel date formula.
How to create and use date formula
1. Open your excel worksheet click where you need the Date created.
2. from the main menu ribbon, click on the Formulas tab.
3. Under the Function Library group, click on the drop-down arrow on the option Date& Time.
4. Select Date from the given options. Excel will display a dialog box.
5. Fill in the given fields representing the date formula.
6. Click OK. Your Date will be created and inserted into the selected cell.
Method 1: using the date formula to return a serial number for a date
For example, when you want to return a serial number that corresponds to March 14, 2021, you use the formula:
=DATE (2022, 2, 25)
But in cases where you do not want to specify the values that represent the year, month, and day directly in a formula, you can use all the arguments in other Excel date functions. You can decide to combine the YEAR and TODAY function as shown below to get a serial number:
=DATE (YEAR (TODAY ()), MONTH (TODAY ()), 14). The result will display a serial number for the fourteenth day of the current month and year.
Method 2: Using the date function to return Date based on values in other cells
When the values meant to create a date sequence are stored in different cells, all you have to do is use the date function. For example, when you have the year in cell A3, the month in cell B3, and the day in cell C3, you can use the function;
=DATE (A3, B3, C3)
Method 3: Using the date function to subtract or add dates in excel
To add or subtract some days from a given date, you first need to convert that Date into a serial number using the excel date function. For example, in the case of adding days to your date function, here is what you do;
=DATE (2022, 2, 25) + 10
When it comes to subtracting, replace the + sign with a minus sign.
Method 4: Using the date formula to convert a string of text or number to a date
The date function is beneficial when it comes to extracting Dates from a string of texts. Such scenarios occur when excel does not recognize the format used. The date function is used in liaison with other functions as stated below;
=DATE (RIGHT, MID, LEFT)
Using Date Formula to Insert Today’s Date
You can use the TODAY function to insert today’s date in any cell in Excel. The function takes no arguments but only returns today’s date. It is represented by the syntax below;
=TODAY()
The only downside is that the TODAY function is not updated automatically. Hence, if you want to update it, just press F9 or re-enter the formula. It also collects the present date from your computer; thus, it will also return a wrong date if the computer's date is wrong.
Using Date Formula to Insert the First Day of Any Month
You can simply use 1 in place of any day argument to insert the first date of any month. It is represented by the syntax
=DATE(yy,mm,dd), where;
dd is the first day of the month, usually represented by 1.
For example; to get the first day of June of 2022, the formula will be:
=DATE(2022,6,1)
However, it becomes a challenge to extract the first date of the month from any date. For instance, assuming you want to extract the first date of the month from 21-February-2020 in cell B4, you can use the formula below;
=B4-DAY(B4)+1
If you want to extract the first date of a month from another date, you can place the date inside the date function as shown below:
=DATE(2020,5,15)-DAY(DATE(2020,5,15))+1
In this case, the DAY function returns the day number of any date inserted into it. For example, DAY(DATE(2022,5,15))+1 will return 15. Since Excel accepts any date as a numerical value, you can add or subtract dates from Excel with any number. Therefore, the above formula subtracts 15 days from the date 1st September 2022 and returns the last day of the previous month, 31-August-2022.
Using Date Formula to Insert the Last Day of Any Month
If you want to insert the last date of any month, enter the last day of that month in the day argument of the DATE function.
For example, to get the last date February 2022, the formula will be:
=DATE(2022,2,28)
However, to extract the last date of the month from any given date, you need to use the EOMONTH function, whose syntax is:
=EOMONTH(start_date,months), where;
Start_date is the last date of the month from which you want to extract the date.
Months denote the number of months you want to move forward. If you want to extract the last date of the present month, it will 0. For next month will be 1, and so on.
For example, if you want to get the last date of the month from 12-February-2022, you can use the formula below:
=EOMONTH(DATE(2022,2,12),0)
To get the last date of the month 3 months later, the formula will become:
=EOMONTH(DATE(2022,2,12),3)
Using Date Formula to Convert Date to Text
You can convert a date into text using the TEXT function of Excel with the following syntax:
=TEXT(value,format_text), where;
Value is the value you want to convert to text.
Format_text. Is the format in which you want the text to appear.
If you want to extract the names of the month of any date, you can use the formula below:
=TEXT(DATE(2022,3,20),"mmmm")
where,
“mmmm” is the format for months.
Using Date Formula to Count Total Number of Workdays Between Two Dates
You can use the NETWORKDAYS function in excel to count the total number of work days between two days. The function is represented by the following generic formula:
=NETWORKDAYS(start_date,end_date,[holidays]), where;
Start_date is the first date.
End_date is the ending date.
[holidays] is a list of holidays.
For example; if you have a dataset with B4:B20 as a list of the holidays, you can get the total workdays between 1-January-2021 to 31-December-2022 using the following formula:
=NETWORKDAYS(DATE(2021,1,1),DATE(2022,12,31),B4:B20)
When you write the above formula in let’s say cell E4, you will get 510 as the total number of working days. However, to count the days without weekends, you use NETWORKDAYS.INTL function.
Using Date Formula to Determine the Workday After a Specific Number of Days
You can use the WORKDAY function in place of the NETWORKDAYS function to determine a specific date after a given number of workdays from a starting date. The function is represented by the syntax:
=WORKDAY(start_date,days,[holidays]), where;
Start_date is the starting date.
Days is the total number of workdays between the two dates.
[holidays] is a list of holidays.
Using the above example where you have B4:B20 as a list of holidays. You can find out the workday after 1000 days starting from 1-January-2021 using the following formula:
=WORKDAY(DATE(2021,1,1),1000,B4:B20)
Using The Date Function to Create A Series Of Dates
You can also use the DATE function to create a series of dates with specific days' intervals. For example, if you want to create interview dates with intervals of 7 days, with 20th May 2021 as the first date, you can proceed with the following steps:
1. Enter the first date in the DATE formula. Assuming you want to use cell C4 as your first case, you can write this formula:
=DATE(2022,5,20)
2. Select all the cells in column C, go to the Home ribbon, and select Fill under the Editing list of options.
3. When you click on Fill, the drop-down menu will appear. Select Series.
4. A new Series dialogue will appear. Select Column from the Series selection, Date from the Type section, and Day from the Date Unit section.
5. Enter your interval between two dates in the Step Value. In this case, you have 7 days as your interval between two dates; hence, write 7.
6. Click OK and you see a series of interview dates displayed in the cells.
You can also use a formula to generate interview dates automatically, especially if you do not want to do it manually. In this case, the formula will be:
=DATE(2022,5,SEQUENCE(17,1,20,7))
where:
17 within the SEQUENCE function is the total number of days of the series (C4:C20).
20 is the starting date of May (20 May).
7 is the dates’ intervals.
Write the formula in cell C4 and click the Enter button. After that, you only use the Autofill Handle to drag the formula down the column to generate interview dates in the remaining cells. You can alter the formula based on your dataset. Also, remember that the SEQUENCE function is only available in Office 365.
Conclusion
From the article above, we get to know how to use Excel's Date formula and how to create a date. The Date function has many more uses that are not mentioned above. I hope you find the information helpful.