Date Functions in Excel Versus SQL Date Functions Compared

Dates are where a spreadsheet and a database agree on the idea and differ on the details: which day a week starts on, how many days a month adds, what counts as a whole month between two dates. A formula that works in a workbook translates into a query with the same shape, and a handful of functions need more than a rename. This post maps the common date functions with worked results for one date, and the places where the numbers differ. It is a spoke of translating Excel formulas into SQL.

Parts of a date

Use the date DATE '2024-03-15', a Friday.

Two notes. TODAY() and NOW() are volatile in Excel, recalculated whenever the workbook changes, and current_date is fixed for the transaction in SQL; for a report that must be reproducible, pass the "as of" date as a parameter instead of reading the clock. And the month and day names that to_char produces follow the database's locale settings, so a report in English should not depend on the server's language.

Moving months: EOMONTH and EDATE

EOMONTH(A1, 0) is the last day of the month of A1, and EOMONTH(A1, 1) the last day of the next month. EDATE(A1, 1) is the same day one month later. In SQL:

SELECT (date_trunc('month', DATE '2024-03-15') + interval '1 month - 1 day')::date AS eomonth_0,
       (date_trunc('month', DATE '2024-03-15') + interval '2 month - 1 day')::date AS eomonth_1,
       (DATE '2024-03-15' + interval '1 month')::date                              AS edate_1,
       (DATE '2024-01-31' + interval '1 month')::date                              AS edate_jan31;

 eomonth_0  | eomonth_1  |  edate_1   | edate_jan31
------------+------------+------------+-------------
 2024-03-31 | 2024-04-30 | 2024-04-15 | 2024-02-29

date_trunc('month', d) is the first day of the month; adding a month and taking away a day gives the last day. Adding an interval of one month to the 31st of January gives 29 February 2024, the last day of the shorter month, which is also what EDATE does. Both clamp rather than overflow into March. Other engines have their own helpers: LAST_DAY(d) in MySQL, EOMONTH(d) in SQL Server, LAST_DAY(d) in Oracle and Snowflake, and DATEADD(month, 1, d) for adding months.

The first day of the next month, written date_trunc('month', d) + interval '1 month', is often better than the last day of this one, because a half-open range (>= first of month AND < first of next month) works for timestamps, where "the last day" would miss the hours after midnight on that day.

Differences between dates: DATEDIF

DATEDIF(start, end, unit) is an undocumented-but-working Excel function that returns whole units between two dates: "d" days, "m" whole months, "y" whole years. SQL has no single function:

SELECT DATE '2024-03-15' - DATE '2024-01-10'                                  AS days,
       (extract(year from age(DATE '2024-03-15', DATE '2024-01-10')) * 12
        + extract(month from age(DATE '2024-03-15', DATE '2024-01-10')))::int AS whole_months,
       extract(year from age(DATE '2024-03-15', DATE '2023-03-16'))::int      AS whole_years;

 days | whole_months | whole_years
------+--------------+-------------
   65 |            2 |           0

Subtracting two dates gives the number of days (65 from 10 January to 15 March 2024, since 2024 is a leap year: 21 days left in January, 29 in February and 15 in March). age(end, start) returns an interval in years, months and days, from which you extract the parts: 2 months and 5 days here, so 2 whole months. The last column asks for the whole years between 16 March 2023 and 15 March 2024, which is 0 because the anniversary has not come yet, one day short. These "whole unit" semantics are what DATEDIF gives and what people mean by age or tenure. DATEDIFF in SQL Server and MySQL is a different function: it counts boundaries crossed, so DATEDIFF(month, '2024-01-31', '2024-02-01') is 1 even though the dates are a day apart. Choose the semantics deliberately.

Weekdays and week numbers

WEEKDAY(A1) returns 1 for Sunday through 7 for Saturday by default, and 2 on the optional second argument makes Monday 1. The 15th of March 2024, a Friday, gives 6 by default. PostgreSQL offers two numberings:

SELECT extract(dow from DATE '2024-03-15')    AS dow,     -- Sunday = 0
       extract(isodow from DATE '2024-03-15') AS isodow,  -- Monday = 1
       to_char(DATE '2024-03-15', 'Day')      AS day_name;

 dow | isodow |  day_name
-----+--------+-----------
   5 |      5 | Friday

For Friday both give 5, which hides the difference; for Sunday dow is 0 and isodow is 7, and Excel's default gives 1. To get Excel's default numbering, use extract(dow from d) + 1. Mismatched weekday numbering is a quiet source of reports where Sunday sales land in the wrong bucket.

Week numbers are worse, because there are several systems. Excel's WEEKNUM(d) default (system 1) counts the week containing 1 January as week 1 and starts weeks on Sunday. ISOWEEKNUM(d), and SQL's extract(week from d) in PostgreSQL, follow the ISO 8601 rule: weeks start on Monday and week 1 is the week with the first Thursday of the year. They agree for most of the year and differ near the turn of the year. For Sunday 1 January 2023, Excel's WEEKNUM returns 1, and the ISO week is 52 (of 2022). Use the ISO functions for anything shared across systems and write the numbering in the report's footnote. If the workbook uses WEEKNUM, reproduce its system explicitly with arithmetic on the day of the year rather than hoping the two match.

Working days: NETWORKDAYS

=NETWORKDAYS(DATE(2024,3,1), DATE(2024,3,15)) counts the weekdays from 1 to 15 March 2024 inclusive: 11 (Friday the 1st, then two full Monday-to-Friday weeks). Add a holiday range as a third argument and those days are skipped. SQL counts weekdays in a generated series:

SELECT count(*) AS networkdays
FROM generate_series(DATE '2024-03-01', DATE '2024-03-15', interval '1 day') AS g(d)
WHERE extract(isodow from g.d) < 6;

 networkdays
-------------
          11

isodow < 6 keeps Monday to Friday. For holidays, join to a table of holiday dates and exclude them: AND g.d::date NOT IN (SELECT day FROM holidays). A calendar table, with one row per date and flags for weekend, holiday and fiscal period, is the cleaner long-term answer, and it makes WORKDAY (add N working days) a query on the table too. Keep in mind that weekends differ by country, and NETWORKDAYS.INTL lets you define them; so does your calendar table.

Dates stored as text, and time zones

All of these assume real dates. A date column that arrived as text or as serial numbers must be converted first (converting Excel serial dates to real dates in SQL, dealing with text that looks like a date in Excel). A timestamp compared to a date includes only midnight on the date; use half-open ranges. Excel dates have no time zone, and a database timestamptz does; convert to the zone the report is read in before taking the date part, or an event at 23:30 UTC lands on the wrong day in Oslo.

Verify

Test the boundaries: the last day of February in a leap year, 31 January plus one month, a Sunday for weekday numbering, the first days of January for week numbers, and a range that spans a holiday. Compare the results with the workbook's cached values, and write down which week numbering and which month arithmetic the workbook used.

When an Excel date formula is better left alone

It is part of an interactive schedule. A project plan where a user changes a start date and sees the end dates move is the spreadsheet's job.

The rules are local and manual. A holiday list maintained by one person in a sheet is a fine source until it needs to be shared; then move it into a table.

It is a quick age or deadline check. A single DATEDIF in a personal workbook does not need a pipeline.

**The workbook relies on TODAY().** A volatile cell makes the output depend on when it is opened; if the result must be reproducible, replace it with an explicit date input, in the workbook or in SQL.

PostgreSQL documents these in its date and time functions reference, and Excel's in Microsoft's date and time functions reference. To check a small extract as JSON, use the CSV to JSON converter.