How To Create A Formula In Excel For Dates

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 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 (2021, 3, 14)

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 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 (2021, 3, 14) + 10

When it comes to subtracting, replace the + sign with a minus sign.

Method 4: Using 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)

Conclusion

From the article above, we get to know some of 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.