Dates and times as serial numbers

A date is a number

Excel stores every date as a whole number: the number of days since 1 January 1900, which is serial 1. So 1 Jan 2025 is 45658 and 31 Dec 2025 is 46022.

What you see as 31-12-2025 is the number 46022 shown through a date format. The number is the value; the format only decides the display.

Because a date is a number, everything you do with numbers works on dates:

• subtract two dates to get days
• add 7 to get the same weekday next week
• compare with < and >
• sort, and take the smallest, largest or average

That is also why a criterion such as "<"&F2 can compare dates in SUMIFS and COUNTIFS.

A time is a fraction of a day

The part after the decimal point is the time of day. One whole day is 1, so:

• 12:00 is 0.5
• 06:00 is 0.25
• one hour is 1/24 (0.041667)
• one minute is 1/1440 (0.000694)

A cell can hold a date and a time together: 46022.75 is 31 Dec 2025 at 18:00. A cell with only a time has no whole part.

To turn a time difference into hours, multiply by 24 (by 1440 for minutes). When a shift ends after midnight, the end time is smaller than the start time and the plain subtraction is negative. MOD(end-start, 1) adds the missing day.

To show totals above 24 hours, use the number format [h]:mm; with plain h:mm Excel starts again after 24 hours, so 30 hours shows as 06:00.

=(C2-B2)*24

=(C2-B2)*24

=MOD(C2-B2, 1)*24

=MOD(C2-B2; 1)*24

=A2+1/24

=A2+1/24

Calculating with dates

Most date work is arithmetic on numbers. The result of subtracting two dates is a number of days, and Excel often gives that cell a date format because the inputs had one: change the format to General or Number, otherwise 35 days shows as 04-02-1900. Subtracting does not count the start day, so 1 Dec to 8 Dec is 7 days.

For calendar logic use the date functions: they know about weekends, month lengths and leap years. NETWORKDAYS counts both the start day and the end day and skips Saturdays, Sundays and an optional list of holidays. EDATE keeps the day of the month where it can: 31 Jan 2026 plus one month is 28 Feb 2026, the last day that exists.

=C2-B2

=C2-B2

=NETWORKDAYS(B2, C2, F2:F12)

=NETWORKDAYS(B2; C2; F2:F12)

=EOMONTH(B2, 0)

=EOMONTH(B2; 0)

=EDATE(B2, 3)

=EDATE(B2; 3)

Formatting changes only the display

Press Ctrl+1 to change how a number is shown. The same cell can display 46022, 31-12-2025, 31 Dec 2025 or Wednesday; the value behind it stays 46022 and every formula keeps using that value. A number shown as a date or the other way round is therefore a formatting question, not a calculation error.

TEXT is different: it returns text. =TEXT(B2, "dd-mm-yyyy") looks like a date but cannot be subtracted or sorted as a date any more.

Format codes are localized: Dutch Excel writes the year as jjjj, English Excel as yyyy, so the same code can fail in the other version. Use TEXT only for the last step, such as a label in a report.

=TEXT(B2, "dd-mm-yyyy")

=TEXT(B2; "dd-mm-yyyy")

=B2

=B2

Dates stored as text

Exports from accounting packages, banks and PDF conversions often hold dates as text that only looks like a date. Excel cannot calculate with it. The signs:

• the value is left-aligned
• ISNUMBER returns FALSE
• a sort puts 02-01-2026 before 29-12-2025
• subtraction gives #VALUE! or a wrong answer

A file in which some dates convert and others do not has usually met the wrong regional setting. DATEVALUE follows your regional setting, so it is only safe when the text uses the same order. DATE with LEFT, MID and RIGHT takes the parts apart yourself and gives the same result in every setting.

For a whole column, Data > Text to Columns converts in place:

• choose Delimited
• press Next twice
• set the column data format to Date and pick the order, for instance DMY

Remove stray spaces first with TRIM; a non-breaking space from a web export needs SUBSTITUTE.

=ISNUMBER(B2)

=ISNUMBER(B2)

=DATE(RIGHT(B2, 4), MID(B2, 4, 2), LEFT(B2, 2))

=DATE(RIGHT(B2; 4); MID(B2; 4; 2); LEFT(B2; 2))

=DATE(LEFT(B2, 4), MID(B2, 6, 2), RIGHT(B2, 2))

=DATE(LEFT(B2; 4); MID(B2; 6; 2); RIGHT(B2; 2))

=DATEVALUE(B2)

=DATEVALUE(B2)

Useful date functions

These functions cover most of what an auditor needs. The ones with a page on this site link to it. Dutch Excel uses the Dutch names and semicolons between arguments; the formulas in the examples on this page are shown in your language.

Limits and quirks

Excel counts 29 Feb 1900 (serial 60) as a real day, although 1900 was not a leap year. The error was copied from Lotus 1-2-3 and kept for compatibility. It has no effect on dates from 1 Mar 1900 onward, which is all audit data, but any date before that is one day off and WEEKDAY is wrong for it.

Excel cannot hold dates before 1 Jan 1900, and a negative date shows as ########.

Some older Mac workbooks use the 1904 date system, which shifts every date by 1462 days; check File > Options > Advanced when dates from a received file look four years off.

Times are stored as binary fractions, so a time calculated by adding fractions can differ from the typed time in the last digits. Then =B2=C2 returns FALSE although both cells show 08:30. Round both to the precision you need before comparing, for instance to whole minutes with ROUND(B2*1440, 0).

Also remember that a date with a time is not equal to the date alone: 31 Dec 2025 14:30 is about 46022.604, so INT removes the time when you need the day.

=ROUND(B2*1440, 0)=ROUND(C2*1440, 0)

=ROUND(B2*1440; 0)=ROUND(C2*1440; 0)

=INT(A2)

=INT(A2)

Related topics

• CriteriaA criterion is the condition a cell must meet to be counted, added up or averaged: how to write exact, comparison, date and blank criteria, and how to combine several.
• OperatorsOperators are the symbols in a formula that calculate, compare, join text or combine references. Excel applies them in a fixed order, which explains many results that look wrong.