Google Sheets 日期與時間公式速查

Which versions this applies to

Every result below comes from Google Sheets (locale zh_CN) on November 12, 2026. The same function names exist in current Excel according to Microsoft’s documentation — DATE, DATEVALUE, EOMONTH, EDATE, DATEDIF, DAYS, WEEKDAY, WEEKNUM, ISOWEEKNUM, NETWORKDAYS, WORKDAY, TEXT, TIME, TIMEVALUE, and YEARFRAC. I did not re-test this set in desktop Excel today, and a US-locale Excel file will not parse 11/03/2026 the way this Sheets file did.

The serial is the real value

DATE(2026,11,12) returned the date 2026-11-12, serial 46338. TODAY() returned 2026-11-12 in the same file, and INT(TODAY())=TODAY() was TRUE, so TODAY() is a date with no time fraction. NOW() returned a datetime (this file displayed it as 2026-11-12 上午06:12:19, serial about 46338.25856). Both TODAY() and NOW() are volatile; do not treat a value you saw once as a constant.

Adding a whole number of days is ordinary arithmetic: DATE(2026,11,12)+7 returned 2026-11-19.

DATE, overflow, and the first of the month

DATE(year, month, day) is the safest way to build a date, because it does not depend on whether the file reads 11/03 as November or March.

Google Sheets also overflows months and days instead of throwing an error:

=DATE(2026,11,12)    → 2026-11-12
=DATE(2026,11,0)     → 2026-10-31
=DATE(2026,13,1)     → 2027-01-01

Day 0 of November is the last day of October. Month 13 is January of the next year. That overflow is useful once you expect it, and easy to miss if a typo in the month argument silently lands in the following year.

The first of the month from a date d:

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

Against DATE(2026,11,12) that returned 2026-11-01. YEAR was 2026, MONTH was 11, DAY was 12. The last day of the month is EOMONTH, below, not DATE(..., 31), which would overflow in shorter months.

DATEVALUE follows the spreadsheet locale

DATEVALUE converts a date written as text into a serial. The string has to match how this file reads dates.

=DATEVALUE("2026-11-03")    → 46329   (3 November 2026)
=DATEVALUE("11/03/2026")    → 46092   (11 March 2026 in this zh_CN file)

Those are not the same day. 11/03/2026 is 11 March here, 237 days earlier than 3 November. In a US-locale workbook the same 11/03/2026 text would be read as November 3. If a column of “dates” was typed by hand, check a mid-month value like the 13th (which cannot be a month number) before you trust DATEVALUE.

ISO text can still do date math in this file: "2026-11-03"-"2026-11-01" returned 2, because Sheets coerced both strings. That coercion is convenient and easy to miss when one cell is a real date and its neighbor is still text. The longer conversion paths are in Excel Dates Stored as Text.

EOMONTH and EDATE

Use these two when you are moving by months. Adding 30 or 31 will drift.

=EOMONTH(DATE(2026,1,15),0)    → 2026-01-31
=EOMONTH(DATE(2026,1,15),1)    → 2026-02-28
=EDATE(DATE(2026,1,31),1)      → 2026-02-28

2026 is not a leap year, so the end of the next month is 28 February, not the 29th. EDATE from 31 January also landed on 28 February, not 3 March. The function clips to the last day of the target month rather than overflowing into the month after. I did not re-test that 31 January case in Excel today; Microsoft describes EDATE as returning the date that many months before or after the start date.

DATEDIF, DAYS, and YEARFRAC

DATEDIF(start, end, "D") and DAYS(end, start) both returned 11 from 1 November 2026 to 12 November 2026. The argument order is not the same: DAYS takes the end date first.

If you swap the dates, DATEDIF does not go negative. DATEDIF(DATE(2026,11,12),DATE(2026,11,1),"D") returned #NUM!. Wrapping that in IFERROR would hide a reversed range. Check the order instead.

A longer span, from 29 February 2020 (a leap day) to 12 November 2026:

=DATEDIF(DATE(2020,2,29),DATE(2026,11,12),"Y")     → 6
=DATEDIF(DATE(2020,2,29),DATE(2026,11,12),"YM")    → 8
=DATEDIF(DATE(2020,2,29),DATE(2026,11,12),"MD")    → 14

That reads as 6 years, 8 months, and 14 days. The "MD" unit counts days after the year and month pieces are removed; I would not use it for anything legally precise without checking the calendar yourself. Age-specific patterns, including birthdays, are in Calculate Age from Date of Birth in Google Sheets.

YEARFRAC for the same 1–12 November window depends on the basis argument:

=YEARFRAC(DATE(2026,11,1),DATE(2026,11,12))      → 0.03055555556   (11/360, default basis 0)
=YEARFRAC(DATE(2026,11,1),DATE(2026,11,12),1)    → 0.0301369863    (11/365, actual/actual)

If you leave the basis off, you are on a 30/360 year, not a 365-day year.

WEEKDAY and week numbers

12 November 2026 is a Thursday. WEEKDAY changes with the return type:

=WEEKDAY(DATE(2026,11,12),1)    → 5    (Sunday = 1)
=WEEKDAY(DATE(2026,11,12),2)    → 4    (Monday = 1)
=WEEKDAY(DATE(2026,11,12),3)    → 3    (Monday = 0)

I treat type 1 as the default only because I passed it explicitly. If a downstream formula assumes Monday = 1, pass type 2.

WEEKNUM(DATE(2026,11,12)) and ISOWEEKNUM(DATE(2026,11,12)) both returned 46 for this Thursday. They are not always the same week around New Year; I did not invent a January example I did not run.

NETWORKDAYS and WORKDAY

The week of 9–13 November 2026 is Monday through Friday:

=NETWORKDAYS(DATE(2026,11,9),DATE(2026,11,13))                          → 5
=NETWORKDAYS(DATE(2026,11,9),DATE(2026,11,13),DATE(2026,11,11))         → 4
=WORKDAY(DATE(2026,11,12),1)                                            → 2026-11-13
=WORKDAY(DATE(2026,11,13),1)                                            → 2026-11-16

Putting Wednesday 11 November in the holiday argument dropped the count from 5 to 4. WORKDAY from Thursday 12 November plus one workday is Friday the 13th; from that Friday plus one workday is Monday the 16th, not Saturday the 14th.

Custom weekends and a longer holiday list are covered in Count Working Days Excluding Holidays.

TIME and TEXT

=TIME(13,30,0)              → 0.5625   (displayed 下午01:30:00 in this file)
=TIMEVALUE("13:30:00")      → 0.5625
=HOUR(TIME(13,30,0))        → 13
=MINUTE(TIME(13,30,0))      → 30

A time is a fraction of a day: 13.5 / 24 = 0.5625. This spreadsheet’s locale showed that fraction as 下午01:30:00, not as 13:30:00. If you need a 24-hour clock in the cell, set a custom number format; do not assume the default time format is English or 24-hour.

TEXT format tokens are written in English, but some output follows the spreadsheet locale:

=TEXT(DATE(2026,11,12),"yyyy-mm-dd")    → 2026-11-12
=TEXT(DATE(2026,11,12),"dddd")          → 星期四

yyyy-mm-dd came out in Arabic numerals, as expected. dddd came out as 星期四 (Thursday in Chinese), not the English word Thursday. If you need a fixed English weekday name, TEXT is the wrong tool in a non-English file.

Time totals that run past 24 hours are a different problem: see Sum Time Over 24 Hours.

Formula table

Common mistakes

• Running DATEVALUE on 11/03/2026 and assuming it is 3 November. In this file it was 11 March.
• Adding 30 to move one month. EDATE or EOMONTH keeps the calendar honest, including the clip from 31 January to 28 February 2026.
• Treating a reversed DATEDIF as zero. It returned #NUM!, not 0.
• Expecting TEXT(...,"dddd") to print an English weekday in a Chinese-locale file. It printed 星期四.
• Using NOW() as a frozen timestamp. It changes when the sheet recalculates.

FAQ

What is the difference between DATE and DATEVALUE?

DATE takes numeric year, month, and day and does not care about locale. DATEVALUE parses a text string and does. DATE(2026,11,3) is always 3 November. DATEVALUE("11/03/2026") was 11 March in this file.

Why did DATEVALUE return a date in March for 11/03/2026?

The spreadsheet locale is zh_CN, which reads that pattern as day/month/year. A US-locale file would read it as month/day/year. Check File > Settings > Locale before you convert a column of slash dates.

How do I get the first and last day of a month?

First day: DATE(YEAR(d),MONTH(d),1), which returned 2026-11-01 from 12 November. Last day: EOMONTH(d,0). Do not write DATE(YEAR(d),MONTH(d),31) unless you want January, March, May, and other 31-day months only; it overflows in April, June, September, and November, and further in February.

What does DATEDIF return if the start date is after the end date?

#NUM! in this test. DAYS with the dates swapped would be a negative number; I only ran DAYS in the forward direction (11).

Do TODAY and NOW stay fixed once I enter them?

No. TODAY() was the calendar date of the test, and NOW() included a time, but both recalculate. If you need a stamp that does not move, paste the value, not the formula.

Will these formulas work in Excel?

The function names are in Microsoft’s Excel documentation. I did not re-run this exact set in desktop Excel today, so I am not quoting Excel serials or Excel TEXT weekday names from this test. Locale parsing of 11/03/2026 will follow the Excel workbook’s regional settings, not this Sheets file.

Related tutorials

• Calculate Age from Date of Birth in Google Sheets
• Excel Dates Stored as Text: 4 Ways to Convert Them to Real Dates
• How to Sum Time Over 24 Hours in Google Sheets
• Count Working Days Excluding Holidays in Google Sheets

Live-test findings

Google Sheets, November 12, 2026, locale zh_CN. DATE(2026,11,12) serial 46338; DATEVALUE("2026-11-03") 46329; DATEVALUE("11/03/2026") 46092; ISO text subtraction "2026-11-03"-"2026-11-01" = 2; EOMONTH on 15 Jan 2026 gave 31 Jan and 28 Feb; EDATE from 31 Jan 2026 plus one month gave 28 Feb; DATEDIF and DAYS for 1–12 Nov both 11; inverted DATEDIF #NUM!; leap-day DATEDIF from 29 Feb 2020 to 12 Nov 2026 was 6 years, 8 months, 14 days; default YEARFRAC 0.03055555556 and basis 1 was 0.0301369863; WEEKDAY types 1/2/3 = 5/4/3; WEEKNUM and ISOWEEKNUM 46; NETWORKDAYS 5, or 4 with a 11 Nov holiday; WORKDAY Thursday+1 = 13 Nov, Friday+1 = 16 Nov; TIME/TIMEVALUE 0.5625; HOUR 13, MINUTE 30; TEXT "yyyy-mm-dd" = 2026-11-12, "dddd" = 星期四; DATE(2026,11,0) = 31 Oct, DATE(2026,13,1) = 1 Jan 2027; TODAY() = 12 Nov 2026 and INT(TODAY())=TODAY() was TRUE.

Sources

• Microsoft Support: DATE function: https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349
• Microsoft Support: DATEVALUE function: https://support.microsoft.com/en-us/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252
• Microsoft Support: Add or subtract dates (EDATE): https://support.microsoft.com/en-us/office/add-or-subtract-dates-b83768f5-f695-4311-98b1-757345f7e926
• Microsoft Support: EOMONTH function: https://support.microsoft.com/en-us/office/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628
• Microsoft Support: DATEDIF function: https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c
• Microsoft Support: NETWORKDAYS function: https://support.microsoft.com/en-us/office/networkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7
• Google Docs Editors Help: DATE: https://support.google.com/docs/answer/3092979
• Google Docs Editors Help: DATEVALUE: https://support.google.com/docs/answer/3093039
• Google Docs Editors Help: DATEDIF: https://support.google.com/docs/answer/6055612
• Google Docs Editors Help: EDATE: https://support.google.com/docs/answer/3092974