These 20+ date formulas can save you lots of time and struggle as you can develop several shortcuts!

Dates are arguably one of the most important aspects of Excel to understand because they are used almost in every scenario of reporting.

The following 20+ Date Formulas are typically used for better navigation and formatting and they are as follows:

1) Using the Drag Button in Excel

If you want to portray multiple dates in a sequence within a column or a row, all you need to do is to start typing a few in sequence, then highlight them, then drag the corner to where you want the sequence to stop. This can be done for numbers, dates, or other sequences in Excel, like months.

Let’s try this for the 3 columns below with headings of period, date, and month.

First, we enter the cells for the period, date, and month to start a certain pattern, in this case an entry for January 1, 2020. Then highlight the 3 cells or each of them independently.

And then finally, simply drag down numbers and dates using the bottom right corner button to auto-populate them in sequence to easily populate sequential data. Your final result would look something like this; a clean set of data!

You can also achieve this if you had the data presented horizontally in columns and you had selected a few and dragged them across. As you can see, this a very useful shortcut to save you time if you select a sequence of data and dragged them either up or down. That way, you don’t have to manually type in data into all these cells. That could be especially useful if you are working with a larger data set.

2) How to Count the Days between Dates in Excel 

You subtract one value from the other to count the days between two dates. For example, we subtract cell B68 from B69 to find out that there 121 days between the two dates, and similarly 391 days between the cells from B70 and B69.

This offers a very quick way to calculate the number of days between dates for purposes such as forecasting!

3) How to Insert Today’s Date in Excel 

This formula in Excel is valuable if you want always want a cell to always contain the most current date as the current date. Simply type in a cell, ‘=today()’

This can be useful as a reference rather than hard-coding a date. As you can see in cell G18, today’s date, as this article was being typed on May 27, 2020.

4) How to Count the Number of Items in a Range in Excel (USING COUNTIF formula) 

The COUNTIF formula, as the name suggests, helps us count the number of items in a range if it meets certain criteria that you can set manually. The COUNTIF formula can be used with dates as well. Let’s say you want to count the number of dates in a list which are more recent than, let’s say, January 1st, 2019. In that case, we would use the COUNTIF formula to reference the list and use the “greater than (>)” to determine if the dates are greater than the reference date.
To Count items in a table if they are more recent than a particular date, enter:

5) How to Count items if greater than a date and less than a date in Excel:

=COUNTIFS(REFERENCE TABLE,”less than”&REFERENCE CELL,REFERENCE TABLE,”greater sign”&REFERENCE CELL)

You should use the COUNTIFS (i.e. plural) formula if you want to set multiple conditions for a lookup and use the date in a table. Use plural when you want to set more than one condition.

6) How to Count items if greater than or equal to a date and less than a date in Excel:

=COUNTIFS(REFERENCE TABLE,”lesser sign”&REFERENCE CELL,REFERENCE TABLE,”greater sign=”&REFERENCE CELL)

Tip 6 is very similar to tip 5. The COUNTIFS formula is used again here but instead of just using the “greater than (>)” or “less than (<)” signs to count the number of dates within a range, we added in an equal (=) sign. If you want to find something greater than or equal to in an Excel formula, just put the two signs beside each other when writing the formula. In cell H26, we are looking for dates greater than or equal to January 1st, 2019, and less than April 1, 2019. So as you can see in the formula, we are using the “greater than (>)” but also, we are putting in the equal sign (=) so that it is greater than or equal to.

7) How to Sum items if greater than a date by in Excel: 

=SUMIFS(DATA TO SUM,REFERENCE TABLE,”greater sign”&REFERENCE CELL)

This tip is about integrating the SUMIFS formula with dates. There are three components to the SUMIFS formula. The first part is to reference the data to be summed. The second part is the reference table itself. The third part is the criteria. Let’s say we want to calculate the sum of the items greater than a particular date. For example, on January 1st, 2019 in the table above, we calculate this in cell H27.

8) Sum items if greater than a date and less than a date in Excel

=SUMIFS(DATA TO SUM, REFERENCE TABLE, “greater sign”&REFERENCE CELL, REFERENCE TABLE, “lesser sign”&REFERENCE CELL)

This is similar to Tip 7 but it just shows that we can have multiple conditions in a SUMIF formula. For instance, if we want to calculate the sum of amounts for column E, from rows 80 to 83, we would write it as the formula shown above. First, we select the sum range which in this case is from cells G80-G83, then we select the criteria range which is F80-F83 from the reference table. Lastly, we set up the range with a selection criterion which is our objective of summing items if greater than a date (January 1, 2019) and less than a date (April 1, 2019).

9) How to determine whether one date is greater or less than another in Excel

The following table is an example of using the IF function to count the number of live contracts. The results from the formula are reflected in the table with a “1” if its live, and a “0” if it is inactive. As you can see, we have 1 new contract per month starting with January and ending in April 2018.

The D34 in the formula is the contract date and is set up to be less than E33 which is the month-end date, and return the value 1. That is if the contract is closed before the month’s end, then portray it as 1, i.e. active, otherwise, put 0 for inactive.

The equation to determine whether one date is greater or less than another: =IF(E59 greater than DATE(yyyy,m,dd), value if true, value if false)

Note also in this formula we put a ‘$’ sign in front of the ‘D’ so that when we drag the formula across, the column does not change with the contract dates. Similarly, we locked the ‘33’ for E33 in the formula so that the row is locked for the date headers being referenced. This application is known as active cell reference and it can be used to lock both the rows and columns when applying ‘$’ sign before both.

10) How to Insert the Month-End date of a referenced cell in Excel

The month-end date formula is a commonly used function in Excel for financial reporting and when creating financial statements. Simply insert the formula of

‘=EOMONTH(reference,0)’ into a cell to quickly formulate the end of month dates.

• As mentioned before, the reference date was taken from the left column of the reference table.
• The ‘End of current month’ for each reference date was found using the EOMONTH formula shown in the middle column.
• The zero is used if you want to obtain the end of month date for the current month.
• Add 1 for each additional month prior to or after the current month.
• For example, the previous month would be -­‐1 and subsequent month would be 1 as shown in the formula section on the left columns.

11) How to Insert the quarter-end text of a referenced cell in Excel

To insert the quarter-end text of referenced cell:
=”Q”&ROUNDUP(MONTH(REFERENCE CELL)/3,0)&” “&YEAR(REFERENCE CELL)

This is very useful if you work in accounting/finance and often work with large data sets across multiple months, quarters, and years and you often need to summarize the data by quarter.

As shown above, after applying the formula, the result appears with the quarter number followed by the year of the given sample date. The G102 is the first reference cell from the sample date column and it follows with G103, and so forth.

12) How to reference periods for financial modeling in Excel

This is a very effective financial modeling tip that can be used in Excel. Let’s say we are creating a model and you have a list of recurring expenses budgeted in a tab. Some of these are payable monthly, others quarterly, and some are annually and a wise tip is to mark them as the payment frequency. Then, in an assumptions tab, you should have this layout that says payment frequency and the number of payments per period, i.e. 1 for monthly, 3 for quarterly, 12 for annually.

This allows you to then do a lookup against this table in the financial model that you’re working on and return the payments number per period so that you could project out costs for the year. This is just a useful tip on how you want to start thinking about modeling data.

We have translated payment frequency (monthly, annually, and quarterly) into actual numbers and created a table upon which we can later reference in our financial modeling through a VLOOKUP or other type of index so that we can start to apply formula.

• Tips 13 through 15 are here to show you how to strip out days, months, and years from a date in Excel.

13) How to return the day of a date reference cell in Excel

For instance, let’s say that you just want to return the day of a date cell. In that case, we would just use the day formula shown on the table above. To return the day of a date reference cell, simply enter ‘=day()’ in a cell, and get a result like 31 for this example.

14) How to return the month of a date reference cell in Excel

Similarly, we can use the month formula to strip out the month from the date. In column F, we have an example date that we want to strip the data from and column H we have the data stripped, i.e. 1. To return the month of a date reference cell, simply enter ‘=month()’ in a cell.

15) How to Return the year of a date reference cell in Excel

To return the year of a date reference cell, simply enter ‘=year()’ in a cell.

This is useful if you are working with large pools of data and want to strip out part of it in a column. You can do that with these formulas and then just copy down the formula beside the table that you are working on to strip all of them out at once. You might want to have a column for day, month, year, each of which is stripping data from a reference cell.

• Tips 16 through 21 show us how to extract text from a date cell rather than just the number.

16) How to return the text day of a week of a reference date in Excel

To return the text day of the week of a reference date in Excel, simply type
‘=TEXT(REFERENCE CELL,”dddd”)’ in a cell.

For instance, tip 16 shows that you can extract the day text by using the formula text and then open parenthesis to put in the reference cell and then putting quotations around the letters “dddd”. This would formulate the result as Thursday which is ultimately the day of the week for the date.

17) How to return the text month of a reference date in Excel 

To return the text month of a reference date in Excel, simply type ‘=TEXT(REFERENCE CELL, “mmmm”)’ in a cell. This is an important tool in Excel to use when analyzing data from financial statements for a specific month.

18) How to extract the 2-digit month from date cell in Excel

To extract the 2 digit month from date cell in Excel, simply type ‘=TEXT(REFERENCE CELL, “mm”)’ in a cell. If you just want the number of months from a given date in Excel, put 2 m’s, ‘mm’, inside the text formula of a cell.

19) How to extract the 3 letter abbreviation month from date cell in Excel 

Another cool feature is using ‘mmm’ which will give you the abbreviated version of the text. For instance, it would write ‘Jan’ for January. To extract the 3 letter abbreviation month from date cell in Excel, simply type ‘=TEXT(REFERENCE CELL, “mmm”)’ in a cell.

20) How to extract the 2-digit year from date cell in Excel

To extract the 2 digit year from date cell, simply type ‘=TEXT(REFERENCE CELL, “YY”)’ in a cell. We can use the ‘yy’ to return the abbreviated version of 2019 to show the last 2 digits, i.e. 19.

21) How to extract the 4-digit year from date cell in Excel

To extract the 4 digit year from date cell in Excel, simply enter ‘=TEXT(REFERENCE CELL, “YYYY”)’ in a cell. We can see here that using 4 Y’s would give us the full year 2019 number on the cell.

22) How to return another date based on the number of months in the future or past in Excel

To return another date based on the number of months in the future or past in Excel, simply type ‘=EDATE(CELL, months)’ in a cell.

This is different from the ‘=EOMONTH’ formula which looked at the month-end date, not the exact date 1 month prior or before like this formula for EDATE is able to achieve.

The date format is given by yyyy-mm-dd and it can be changed depending on your preference of formatting.

LASTLY, this table a culmination of all the formulas used in this lesson above and summarizes the most effective date formulas in Excel!

The left side of the table has some reference sample dates. The next column returns the day using the day formula, then the following column returns the month using the month formula, and we have the year to return that as well.

To the right of that, we use the text formula and return the full name of the day, then we use the EDATE formula to return the prior month day, and then the month ahead.

Finally, in the last column, we convert the date to a quarter-end reference using the quarterly reference formula which we reviewed in Tip 11.

These summarize the key date formulas in Excel that you should practice and build it to your daily financial modeling, analysis, or other Excel work. This will ultimately save you time in the analysis that you performed.

Now that we know these formulas are available, when you perform your next tasks on a day to day basis we should ask ourselves before doing the analysis whether any of these formulas are relevant to be used. If the answer is yes, don’t be hesitant to start using them and integrating date formulas into your Microsoft Excel activities!!

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!