Date functions and formulas in Excel

7 Excel date formulas explained for beginner to intermediate levels: TODAY, NOW, ISOWEEKNUM, SUMIFS, etc. Get ready to learn with the help of Ted Jordan.

Microsoft Excel offers a multitude of date functions and formulas: DATE, TODAY, NOW, WEEKDAY, etc. There are about 20 dates functions you can use in Excel. These date and time functions are handy to calculate deadlines, prioritise tasks, plan holidays or convert text to date data.

Today, we’ll share with you 7 date functions and formulas to use in Excel + 1 bonus tip.

These date functions are mainly used by Excel professionals or considered as Excel date functions you must know by our students. Yes, Ted Jordan provides analytics online courses such as the Excel: Beginner to Advanced course.

Excel date formulas

Today, learn how to use the following Excel date functions and formulas:

You’ll be able to check when a worksheet was last refreshed, set automatic deadlines, create week numbers from dates, know what day was a specific date, and much more!

TODAY function

The TODAY function allows you to insert today’s date in Excel. You can also use it to calculate the number of days before a deadline.

The TODAY formula is =TODAY()

Depending on your Excel date settings, the date may be displayed as dd/mm/yyyy or mm/dd/yyyy.

See TODAY function examples.

NOW function

The NOW function allows you to display the current date and time in Excel. You can also use it to calculate the number of days before a due date in an exact matter.

The NOW formula is =NOW()

What’s the difference between TODAY and NOW in Excel?

WEEKNUM function

The WEEKNUM function returns the week number of a specific date. Its formula is a bit more complex than the previous date functions.

The Excel WEEKNUM formula is =WEEKNUM(serial_number,[return_type])

  • serial_number: this argument is required. It can be a serial number, a date or the result of a formula in a date format.
  • return_type: a number that determines the type of return value to use, to decide on which day a week begins. If you don’t enter any value, the system will consider Sunday as the 1st day of the week. For Monday, enter 2.

How to use WEEKNUM in Excel? (with examples)

If you work with the ISO system for your calendar or financial year, use ISOWEEKNUM instead.

ISOWEEKNUM function

The ISOWEEKNUM function converts a date to a week number using the ISO system. This Excel date function can be used with the DATE function if needed.

The ISOWEEKNUM formula is =ISOWEEKNUM(date)

  • date: this argument is required. It can be a serial number, a date or the result of a formula in a date format.

How to use ISOWEEKNUM? (with examples)

WEEKDAY function

The WEEKDAY function returns the day of the week corresponding to a specific date. This Excel date function is similar to WEEKNUM.

The WEEKDAY formula is =WEEKDAY(serial_number,[return_type]) in Excel.

  • serial_number: this argument is required. It can be a serial number, a date or the result of a formula in a date format.
  • return_type: this argument is optional. It’s a number that determines the type of return value to use.

Learn how to use WEEKDAY in Excel.

TEXT function

The TEXT function is really handy when working with dates in Excel. This function allows you to show the name of a day from a date in Excel; convert dates to text values too with TEXT.

The TEXT formula is =TEXT(Value you want to format, “Format code you want to apply“)

To show the name of the day for a specific date, TEXT formulas can be:
=TEXT(date,”dddd”)
=TEXT(date,”ddd”)

  • date: a cell containing a date or a formula resulting in a date value.
  • dddd or ddd: dddd will result in the name of a day in its entirety and ddd will result in an abbreviated name of a day.

TEXT function examples with name of the day

To convert dates to text in Excel, use one these formulas:
=TEXT(value,”dd/mm/yyyy”)
=TEXT(value,”dd/mm/yy”)
=TEXT(value,”mm/dd/yyyy”)

Learn how to convert dates to text (with examples)

SUMIFS function

Use the SUMIFS function in Excel to calculate the invoices total during a specific time range. For example, all the invoices for Q1 (from January 1st to March 31st).

The SUMIFS formula is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

  • sum_range: the range of cells to sum.
  • criteria_range1: the range where the first criteria is checked.
  • criteria1: the first criteria checked, defining which cells will be added from criteria_range1.

To sum data between two dates with SUMIFS:

  • Enter =SUMIFS( in the cell where you want the result to be displayed
  • Add the range of cells to sum followed by a comma (,)
  • Enter the range where dates appear followed by a comma(,)
  • Enter “>=dd/mm/yyyy“, with dd/mm/yyyy being the oldest date in your time range
  • Again, add the range where dates appear followed by a comma(,)
  • Then, enter “<=dd/mm/yyyy“) with dd/mm/yyyy being the most recent date in your time range
  • Finally, click Enter.

Example of SUMIFS with 3 criteria.

From text to date in Excel

You just discovered 7 date functions and formulas to use in Excel. Ted Jordan wanted to give you another tip when working with dates: how to convert text to date with Text to Columns.

If you have issues trying to sort out dates in your Excel spreadsheets, it might be because dates are written in different formats. The solution is easy: use Text to Columns to convert dates written as text to date values.

How to change date format with Text to Columns? Click to learn how.

Learn more Excel tips

Did you enjoy learning how to use date functions and formulas in Excel with our free tutorials? Do you want to become proficient in Excel and receive a Professional Certificate?

Join Ted Jordan’s Excel Spreadsheet Course.

The online course has been designed for beginners to intermediate levels and is available now. What are you waiting for?

Advertising
Analytics

Categories

AllAdvertisingFormatsPlatformsKPI'sAd News
What is Programmatic Advertising?

What is Programmatic Advertising?

Understand what programmatic advertising is and how it works to increase your ROI and become a programmatic specialist.

Read More
Programmatic Advertising: What is a DSP?

What are DSPs?

A DSP is a great tool to use but what does DSP stand for in marketing? What are some Demand-Side Platforms examples? Today, we answer your questions!

Read More

Best DSPs for Programmatic Advertising

Ted Jordan shares his list of best DSPs for Programmatic Advertising so you don’t have to lose time searching the internet to find the right Demand-Side Platform for your campaigns.

Read More
Programmatic Advertising: What is a SSP?

Supply-Side Platforms (SSPs) Explained

SSPs basics in advertising: learn what Supply-Side Platforms are used for and who use them. Get familiar with more programmatic advertising technical terms.

Read More

What is O&O Advertising?

Discover one more important jargon term used in Programmatic Advertising: O&O advertising. Get familiar with O&O formats and advantages.

Read More
Programmatic: Pixels in Advertising 101

Advertising pixels: types, pros and best practices

Learn the basics of Programmatic Advertising with Ted Jordan! Today, we explain what a pixel is in advertising with simple words and expertise. Ready to learn?

Read More

Categories

AllFunctionsToolsShortcut Keys

How to change shapes in Excel column charts

Learn how to easily change the columns shape in your Excel charts so your colleagues and customers will remember your impacting graphs.

Read More

How to use the LEFT, RIGHT and MID functions

Learn how to extract text in Excel with the LEFT, RIGHT and MID functions. Plenty of examples to learn from and functions are explained with simple words. If you start...

Read More

Excel IF statement: how to use it for 3 conditions

Learn how to use the Excel IF statement with multiple conditions and combine it with OR and/or AND functions!

Read More
Navigating with Tab, Enter, and Shift Key on Excel.

How to move between cells when arrow keys are not working (Excel)

Learn how to move between Excel cells without using your mouse or arrow keys. Discover more shortcuts and tricks below!

Read More

How to use the IFERROR function (Excel + Google Sheets)

Discover how to use IFERROR in Excel or in Google Sheets in 4 easy steps! No more errors on your documents or dashboards with IFERROR.

Read More
Cope Paste Options on excel with shortcut keys

Copy paste values like a pro! (Excel+Google Sheets)

Copy and paste values (not formulas) using shortcut keys in Excel or Google Sheets! Learn how spreadsheets professionals do it within seconds and become one of them. Save time now...

Read More