DATEVALUE Function (Gnumeric)

=DATEVALUE(serial)

serial is one required numeric date/time serial, or a value/text that Gnumeric’s date-time parser can recognize. There are no optional parameters or documented defaults. Gnumeric calls its date-time serial conversion helper and returns the whole-day date serial. The time-of-day fraction is not kept. [S1][S2]

The Gnumeric 1.12.62 official public catalogue marks DATEVALUE Complete / Basic testing. The source demonstrates a numeric =DATEVALUE(NOW()) example. This supports numeric date-time input on Gnumeric even though other programs commonly describe DATEVALUE principally as text conversion. Source presence and an upstream testing label are not equivalent to running the examples in this guide. [S1][S3][S5]

On a narrow screen, scroll this table sideways to see every column.

Expected: in a 1900-system workbook, D2 as a number is 45351, or 2024-02-29 with a date format. E2 is approximately 0.6458333333, or 15:30:00 with a clock-time format. The serial calculation is independently checked against calendar arithmetic (including the historical 1900 compatibility adjustment); no native Gnumeric execution is claimed. [S1][S2][S4]

Also try:

=DATEVALUE(DATE(2024,3,1)+TIME(23,59,59))

Expected: 45352 as a number under the conventional 1900 date system, because the fraction before midnight is removed. Display as a Date if you want to see March 1, 2024 instead. [S1][S2]

=DATEVALUE("2024-02-29") might be useful for some imported text, but its acceptance depends on Gnumeric’s date/time parser and locale. The versioned converter uses format_match_number for text values; that does not establish identical parsing behavior with Excel or LibreOffice in all regional settings. Treat the direct text formula as documentation-check-only until tested on the target system. [S2][S4]

For a CSV file with separate numeric Year, Month, Day columns, construct dates predictably using:

=DATE(A5,B5,C5)

For example A5=2024, B5=2, C5=29 should produce February 29, 2024. This avoids locale-sensitive parsing of strings such as 02/03/2024 (February 3 versus March 2). [S4]

Daily grouping of timestamps: if A8 contains a real Gnumeric numeric timestamp, put =DATEVALUE(A8) in B8, format B8 as a date, and group records by that value. Do not use formatted display strings as grouping keys, because strings can sort or parse differently. [S1][S2]

Separate date and time without losing the original:

=DATEVALUE(A8)
=TIMEVALUE(A8)

Use the first in a date column and the second in a time column; keep A8 for the original date-time. If the timestamp is guaranteed to be a nonnegative numeric serial, =INT(A8) and =MOD(A8,1) are useful arithmetic alternatives to investigate when transferring to other spreadsheet programs. [S1][S2]

Date conventions matter: in Gnumeric/Excel-compatible 1900 serial conventions, an extra serial slot accounts for the historical fictitious 1900-02-29. Workbooks using a 1904 base yield different serial numbers for the same calendar day; comparing printed serials without checking the date system can give misleading results. The safest reproducible formulas for a shared guide construct dates with DATE instead of writing literal serial constants. [S4]

• A number such as 45351 is displayed: it is the underlying date serial, not a failure. Apply a date format. [S1][S4]
• A time disappears: DATEVALUE returns the date portion. Retain the full source in a separate cell, or use TIMEVALUE for the time. [S1]
• Text date seems reversed or not recognized: check regional date order, data type, whitespace and a known-good DATE constructed from numeric fields. [S2][S4]
• Bad text might not yield Excel’s #VALUE!: the Gnumeric internal converter uses a sentinel for parse failures and the DATEVALUE implementation does not itself create a #VALUE! return in that branch. Do not treat a numeric result as proof that arbitrary text parsed correctly; inspect the resulting date and native-test invalid strings. [S1][S2]
• Dates mismatch another spreadsheet: confirm 1900 versus 1904 date conventions and ensure both cells are real dates rather than text strings. [S4]

Gnumeric’s official help labels DATEVALUE Excel-compatible, but a meaningful difference remains: Gnumeric’s 1.12.62 source accepts numeric date/time serials, whereas Microsoft’s published DATEVALUE(date_text) contract expects text and describes a specific #VALUE! error for invalid/out-of-range text. Avoid presenting a Gnumeric numeric-input example as universally interchangeable. [S1][S2][S5]

Related functions: DATE, YEAR, MONTH, DAY, TODAY, NOW, TIME, TIMEVALUE.

• [S1] Gnumeric 1.12.62 DATEVALUE documentation embedded in tagged source — argument, return path, official example.
• [S2] Gnumeric 1.12.62 date/time numeric and text serial conversion — text/number handling and sentinel caveats.
• [S3] Gnumeric public function catalogue — DATEVALUE Complete / Basic.
• [S4] Gnumeric source DATE serial convention and official locale format guidance — calendar serial/locale context.
• [S5] Microsoft DATEVALUE documented text interface — comparison only.

The syntax and behaviour are based on the Gnumeric references cited above. The sample calculations were independently checked; they are expected results, not outputs observed in Gnumeric. Test locale-sensitive text conversion and imported files in your own installation. View the example-check record.

Official DATEVALUE reference · Editorial standards