Excel Day and Date Formulas: Show Days, Dates, and Names

In Excel, use =DAY(A2) to return the day of the month from a date in A2, or =TEXT(A2,"dddd") to show its weekday name. To build a date from separate year, month, and day values, use =DATE(A2,B2,C2); the right formula depends on whether you need a date, a day number, or a weekday label.

What “day and date” can mean in Excel

People searching for an Excel day and date formula may be asking for several different results. A date such as July 19, 2026 contains a year, a month, and a day of the month, and it also falls on a particular weekday. Excel has different functions for extracting each part or displaying the date in a preferred form.

Day of the month: a number from 1 to 31, such as 19.
Weekday: a day name such as Sunday, or a weekday number.
A complete date: a calendar date assembled from year, month, and day values.
Today’s date: the current date, which updates when the workbook recalculates.

These results are not interchangeable. For example, the number 19 by itself does not identify a date or a weekday. The month and year matter, and the weekday depends on the complete date.

Get the day of the month with DAY

The DAY function returns the day number from an Excel date. With a valid date in A2, enter this in another cell:

With the date July 19, 2026 in A2, the formula returns 19:

=DAY(A2)

DAY returns a number, not text. That makes it useful for calculations, sorting by day number, or checking whether a date falls near the start or end of a month. The result will be between 1 and 31, depending on the month; February dates, for example, can return only 1 through 28 or 29.

A common misconception is that DAY(A2) gives the weekday. It does not: it extracts the calendar day within the month. Use WEEKDAY or TEXT when you need to know whether the date is Monday, Tuesday, or another weekday.

Show the weekday name or number

To display a weekday name, TEXT formats a date as text. With July 19, 2026 in A2, the result is Sunday:

=TEXT(A2,"dddd")

Use three d characters for an abbreviated name. With the same date in A2, this returns Sun:

=TEXT(A2,"ddd")

The result of TEXT is text, so it is convenient for labels and reports. If you need a numeric weekday for a rule or calculation, use WEEKDAY instead. With July 19, 2026 in A2, the following returns 1 because the default numbering treats Sunday as 1 and Saturday as 7:

=WEEKDAY(A2)

You can choose a different numbering system with WEEKDAY’s second argument. With that same Sunday date in A2, this returns 7, using Monday as 1 and Sunday as 7:

=WEEKDAY(A2,2)

This Monday-first convention is often helpful for work schedules. Under it, Monday through Friday are 1 through 5, and Saturday and Sunday are 6 and 7. Pick a return type deliberately: a formula that assumes Sunday is 1 will interpret the number differently from one that assumes Monday is 1.

Build a date from separate values

DATE combines a year, month, and day into a genuine Excel date. Suppose A2 contains 2025, B2 contains 3, and C2 contains 8. Enter this formula in D2:

=DATE(A2,B2,C2)

The result is March 8, 2025, which is a Saturday. The way it appears in D2 depends on the cell’s number format, but the underlying result is a date value that Excel can sort and use in calculations.

DATE is useful when importing data that stores year, month, and day in separate columns, or when making a date from values entered in a form. It also normalizes values that go beyond a month’s usual range. For example, with year 2025 in A2, month 13 in B2, and day 1 in C2, the formula returns January 1, 2026. This behavior can be useful, but it can also conceal incorrect source data, so validate inputs when unexpected month values should be treated as errors.

For years from 0 through 1899, DATE adds 1900 to the year argument. Thus a year argument of 125 produces 2025, not the year 125. For dates before 1900, Excel’s standard date system has limitations; do not assume DATE handles historical dates the same way as a modern calendar database.

Enter today’s date and the current date and time

TODAY returns the current date, without a time. Type this directly into a cell where you want the date to appear:

=TODAY()

NOW returns the current date and time. Enter it in a cell when you need both:

=NOW()

These are volatile functions: their displayed results can update when Excel recalculates the workbook. They are not fixed timestamps. If you need a permanent date stamp for a record, enter a static date instead of relying on TODAY or NOW. On Windows, Ctrl+; inserts the current date as a fixed value, and Ctrl+Shift+; inserts the current time. On Mac, the date shortcut is Control+; and the time shortcut is Command+; in current Excel for Mac; keyboard mappings can vary with system settings.

Whether TODAY or NOW displays as a serial number or a readable date depends on the cell format. If the result looks like a large number, format the cell as a date or date and time rather than changing the function.

Format dates without changing their value

A date in Excel is stored as a serial number, with the displayed calendar date controlled by formatting. This distinction matters: applying a different date format changes how the value looks, not the date itself. For example, a cell can show 19-Jul-2026, July 19, 2026, or 7/19/2026 while retaining the same underlying date.

To change a date’s display in Excel for Microsoft 365 on Windows, select the cells, open Home > Number Format, and choose a date option. For more control, press Ctrl+1 to open Format Cells, select the Number tab, and choose Date or Custom. On Mac, use Home > Number Format or press Command+1 to open Format Cells. Excel for the web has date choices on the Home tab, though its formatting controls are not identical to the full desktop dialog.

Custom formats can show weekday names alongside dates. For example, a custom format using dddd, mmmm d, yyyy displays a full weekday and date, such as Sunday, July 19, 2026. In a custom number format, dddd means full weekday name, ddd means abbreviated weekday, dd means a two-digit day of the month, and d means an unpadded day number. Use TEXT when you need a text result in a separate cell; use cell formatting when you want to keep the cell’s value as a date for calculations.

Make a date that stays in the same month

To get the first day of the month containing a date in A2, use DATE with YEAR and MONTH. With July 19, 2026 in A2, the result is July 1, 2026:

=DATE(YEAR(A2),MONTH(A2),1)

To calculate the last day of that month, use day zero of the following month. With July 19, 2026 in A2, the result is July 31, 2026:

=DATE(YEAR(A2),MONTH(A2)+1,0)

This approach handles different month lengths and leap years without hard-coding 28, 30, or 31. It is helpful for monthly reports, billing periods, and date-based summaries. If the task is to add a number of days to a date, Excel can also add that number directly to a valid date serial; for more complex month-end rules, be explicit about the desired behavior rather than assuming that adding a month always preserves the same day number.

Why a date formula may show a number or fail

If a formula returns a number such as 46222 instead of a familiar date, the formula may be working: Excel stores dates as serial values. Apply a date format to the result. Conversely, a value that looks like a date can be stored as text, in which case date functions may return an error or an unexpected result.

Common causes and fixes include:

The source date is text: confirm that Excel recognizes it as a date, then convert or re-enter the value using a format Excel understands.
The formula returns a weekday number you did not expect: check the second argument of WEEKDAY and make sure your chosen numbering convention matches the formula.
The name appears in another language: TEXT uses the workbook’s regional and language settings for month and weekday names. The same format code may show localized names.
The displayed date looks reversed: date interpretation can depend on regional settings, such as month/day/year versus day/month/year. Verify the actual date value rather than guessing from ambiguous text like 03/04/2026.
The result does not update: TODAY and NOW update through recalculation. Check Formulas > Calculation Options and whether calculation is set to Automatic.

Excel workbooks can use the 1900 or 1904 date system, and workbooks created on different platforms or with different settings may use different systems. This can affect serial values when dates are copied between workbooks. For normal new workbooks, enter and display dates as dates rather than manually copying serial numbers.

Choosing the right formula

Choose the function based on the result you need, not just on what the source cell looks like. Use DAY when the answer should be a day-of-month number, WEEKDAY when you need a weekday number for logic, and TEXT when you want a weekday name as text. Use DATE when your source gives separate year, month, and day components. TODAY and NOW are for the current date or current date and time, not for fixed historical entries.

For example, a schedule may need a weekday label for a person reading it and a numeric weekday value for a formula that marks weekends. Those are separate needs, and it is reasonable to use a display formula in one column and a calculation formula in another. Keep original date values intact when possible: a real date supports sorting, filtering, date arithmetic, and chart axes more reliably than a date written as plain text.

Frequently Asked Questions

What formula returns the day of the week in Excel?
Use TEXT with a date cell and the weekday format code. For a full name, use dddd; for an abbreviation, use ddd. If you need a number rather than a name, use WEEKDAY and select the numbering system you want.

How do I get just the day number from a date?
Use DAY with the cell containing the date. It returns a number from 1 to 31, such as 19 for July 19. It does not return the weekday.

How do I enter today’s date in Excel?
Use TODAY() for a date that can update when the workbook recalculates. To insert a date that remains fixed, use the keyboard shortcut for inserting the current date or type the date as a value.

Why does Excel show a number instead of a date?
Excel stores dates as serial numbers. Select the result cell and apply a date format through Home > Number Format, or use Format Cells to choose a date display.

How can I display the weekday and date together?
Apply a custom date format that includes weekday and date elements, such as dddd, mmmm d, yyyy. This keeps the cell’s value as a date. Use TEXT instead if a separate text label is specifically required.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.