Excel stores dates as serial numbers and times as fractions of a day, so you can format them for display and use them in calculations. Enter a valid date or time, then choose a suitable format with Home > Number Format; for example, add a date in A2 and a time in B2, and use =A2+B2 to combine them into one date-time value.
Why Excel treats dates and times as numbers
A date looks like a calendar entry, but Excel needs a form it can calculate with. In the standard 1900 date system used by most Windows workbooks, a date is represented by a whole-number serial value. The number increases by one for each day. A time is the fractional part of a day: midnight is 0, noon is 0.5, and 6 p.m. is 0.75. A date-time combines both parts in one number.
This design lets you subtract two dates to find the number of days between them, add a duration to a start time, or sort dates chronologically. The displayed appearance is separate from the stored value: the same value can appear as 2-Nov-2026, 11/2/2026, or a serial number depending on its number format.
There is a historical quirk in the 1900 date system. To remain compatible with older spreadsheet software, Excel treats 1900 as if it had a February 29, even though it did not. This affects serial values around that date, not ordinary modern dates. It is one reason not to treat Excel serial numbers as a universal date standard outside a workbook.
Entering dates and times reliably
Type dates in a form Excel recognizes for your regional settings, such as 11/2/2026 in a U.S. setup or 2/11/2026 in many other locales. Those two entries can mean different dates, so an ambiguous numeric date is risky when a file moves between people or regions. A four-digit year helps, and an unambiguous form such as 2 Nov 2026 is often easier to interpret. ISO-style dates such as 2026-11-02 are also useful for data exchange, though recognition can still depend on how a value is imported.
For times, type values such as 2:30 PM or 14:30. Excel may display a time in a different style from the one you typed because the cell’s number format controls its appearance. If Excel leaves the entry aligned like text or refuses to calculate with it, the value may have been imported or entered as text rather than recognized as a date or time.
- For a fixed date, use a four-digit year and a clear month name when practical.
- For imported data, check whether the source uses day-month-year or month-day-year before converting it.
- For an exact date or time assembled from parts, use Excel’s DATE or TIME functions rather than relying on ambiguous text.
With no input cells required, =DATE(2026,11,2) returns the date November 2, 2026, as an Excel date value. With no input cells required, =TIME(14,30,0) returns 2:30 p.m. as a time value. These functions are especially useful in templates, because the year, month, day, hour, minute, and second are explicit.
Formatting dates, times, and durations
To change how a value appears, select the cell and use Home > Number Format. Choose a built-in date or time style, or select More Number Formats to set a custom format. You can also open Format Cells with Ctrl+1 on Windows; on Mac, use Command+1. Formatting changes the display, not the underlying date or time value.
Common custom date codes include d for day, m for month, and y for year. For example, dd-mmm-yyyy displays 02-Nov-2026, while yyyy-mm-dd displays 2026-11-02. In a time format, h or hh displays the hour, mm displays minutes, and ss displays seconds. The meaning of m depends on its context: near hours or seconds it represents minutes, while in a date format it represents the month.
Elapsed time needs particular care. A normal time format wraps after 24 hours, so a total of 27 hours may display as 3:00. To show accumulated hours, select a custom format such as [h]:mm; the square brackets tell Excel to display elapsed hours instead of restarting the hour count each day. Use this for shift totals, project durations, or other sums that can exceed one day.
Calculating with dates and times
Once Excel recognizes values as dates and times, arithmetic is straightforward. Adding a whole number to a date moves it forward by that many days; subtracting one date from another gives a day count. Adding a time fraction advances the date-time by part of a day. Make sure the result cell has a date, time, or duration format that makes the answer readable.
With a start date in A2 and an end date in B2, =B2-A2 returns the number of days between them. If A2 is November 2, 2026, and B2 is November 5, 2026, the result is 3. Format the result as a number if you want a day count; a date format would make that number look like a calendar date instead.
With a date in A2 and a time in B2, =A2+B2 combines them. If A2 contains November 2, 2026, and B2 contains 2:30 p.m., the result represents November 2, 2026 at 2:30 p.m. Apply a date-and-time format to see both components.
With a start time in A2 and an end time in B2, =MOD(B2-A2,1) calculates an elapsed time when the interval may cross midnight. For a shift from 10:00 p.m. to 6:00 a.m., the result is 8 hours; format it as [h]:mm or h:mm. This example assumes the shift is shorter than 24 hours. For a multi-day interval, store full date-time values and subtract those instead of using times alone.
Useful date and time functions
DATE and TIME build valid numeric values from separate components. TODAY returns the current date, and NOW returns the current date and time. These are volatile functions: Excel refreshes them when the workbook recalculates, so they are not a way to preserve the date a record was originally entered. If a date must remain fixed, enter it as a value rather than leaving a live formula.
With no input cells required, =TODAY() returns the current date according to the computer’s date at recalculation. With no input cells required, =NOW() returns the current date and time at recalculation. Format the result appropriately; NOW may appear to show only a date if the cell uses a date-only format.
For a timestamp that updates when recalculated, NOW is suitable. For a static entry timestamp, a user can enter the current date with Ctrl+; on Windows, or Command+; on Mac, and the current time with Ctrl+Shift+; on Windows. Shortcut behavior can vary with keyboard layouts. These shortcuts insert values, not formulas, so the timestamp does not keep changing.
When a date is stored as text, DATEVALUE can convert recognized date text into an Excel date value. With a date string in A2 that Excel can interpret under the current regional settings, =DATEVALUE(A2) returns its serial date value, which you can then format as a date. If the source text is ambiguous or uses an unsupported format, conversion may fail or produce an unintended date; verify the result rather than assuming the conversion understood the source’s locale.
Common problems and how to fix them
A cell showing a number such as 46328 may contain a valid date with the General or Number format. Apply a date format to display it as a calendar date. Conversely, changing text that merely looks like a date to a date format does not necessarily convert it: number formatting cannot turn text into a numeric date value.
If a formula returns a number that looks strange, check the result’s format as well as the formula. A date subtraction may need a Number format to show days; an elapsed-time calculation may need [h]:mm. If a date displays as a row of hash marks, the column may simply be too narrow; widen it. Negative date-time results can also display as hash marks under the standard 1900 date system, so check whether the end value is earlier than the start value.
- Unexpected month and day: Check the source’s regional date order and inspect the actual stored value.
- Formula does not calculate: Check whether one or more inputs are text, and confirm that the cell is not formatted as Text.
- Time total resets at 24 hours: Apply the custom elapsed-time format [h]:mm.
- Dates appear shifted after import: Check the source timezone, date order, and workbook date system before editing values.
To change a cell from Text to General, select it and use Home > Number Format > General, then re-enter or convert the value; changing the format alone may not re-parse existing text. For a column of imported values, use the import or conversion tools carefully and check a few converted rows against the original source. Avoid replacing a whole column until you have confirmed what Excel interpreted.
Date systems, time zones, and compatibility
Excel workbooks can use the 1900 or 1904 date system. The 1900 system is the usual choice for Windows workbooks; the 1904 system is associated with some older Mac workbooks. The same calendar date has serial values that differ by 1,462 days between the systems. When workbooks using different systems exchange raw date values, dates can appear shifted unless the values are converted appropriately.
On Windows, the workbook option is under File > Options > Advanced, in the section for calculating this workbook; the setting is labeled Use 1904 date system. On Mac, date-system settings and their location can vary by Excel release, so check the workbook’s calculation settings. Changing a workbook’s date system can affect existing date values; do not toggle it casually in a file that already contains dates. Compare dates before and after any change and keep a backup.
Excel’s ordinary date-time values do not carry a time-zone identifier. A value that says 9:00 a.m. does not by itself tell you whether that is UTC, local time, or a particular daylight-saving offset. If data comes from an online service or another time zone, retain the timezone information in a separate field or normalize the source values before analysis. Daylight-saving transitions can also make local clock times ambiguous or nonexistent, which a plain Excel time cannot resolve on its own.
Frequently Asked Questions
Why does Excel show a date as a number?
Excel stores dates as serial numbers. Select the cell and apply a date format using Home > Number Format; if it remains a number, check that the value is a real date and not text.
How do I combine a date and time in Excel?
Put the recognized date in one cell and time in another, then add them. Format the result with a date-and-time style to show both parts.
How do I calculate hours across midnight?
If A2 contains the start time and B2 the end time, use =MOD(B2-A2,1) for an interval under 24 hours, then format the result as [h]:mm.
Why does my Excel date change when I open another workbook?
The workbooks may use different date systems, or the imported values may have been interpreted using different regional settings. Check both the source format and the workbook’s 1900 or 1904 setting.
Does Excel store a time zone with a date and time?
No. A normal Excel date-time value stores a date and fraction-of-day time, not a timezone or UTC offset. Keep timezone details separately when they matter.
Related
- Markdown Table Merge Cells? What You Can Do
- PHP Backend Data Validation: A Practical Guide
- Protect Sheet in Excel: Allow Editing in Certain Cells
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Validation Custom Formula Examples That WorkExcel custom data validation lets you accept an entry only when a formula returns TRUE. In Microsoft 365…
- Excel NPV Formula Year by Year: How It WorksIn Excel, use =NPV(rate, year_1_cash_flow:year_n_cash_flow)+year_0_cash_flow to calculate net present value when cash flows occur at the end of…
- AVERAGEIF Excel Between Values: Formula and ExamplesTo average numbers that fall between two limits in Excel, use AVERAGEIFS, not AVERAGEIF, because the range needs…
- SUMIFS Excel: Sum Values That Meet Multiple CriteriaSUMIFS is Excel’s conditional-sum function: it adds values only when all the conditions you specify are met. With…
- Excel Text Filter for Multiple Words: How It WorksTo filter Excel text for multiple words, open the column’s filter menu and choose Text Filters > Custom…
- Excel IFS for Blank Cells: Formulas and Common FixesTo make Excel’s IFS function return a result when a cell is blank, test for a blank first:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.