How to Use the DATEVALUE Function in Excel

Introduction

The DATEVALUE function in Excel and Google Sheets is designed to convert a text representation of a date into a serial number that Excel uses to represent dates. This function is particularly useful when dealing with date data stored as text, enabling you to perform calculations or comparisons effectively.

Syntax

The syntax for the DATEVALUE function is as follows:

DATEVALUE(date_text)

date_text: The text string that represents a date, which you wish to convert into a serial number.

Excel Example

Consider a scenario where you have a date in text format in cell A1: “01/15/2022”. You can use the DATEVALUE function to convert this text to a serial number that represents the date.

A	B
01/15/2022	=DATEVALUE(A1)

In cell B1, enter the formula =DATEVALUE(A1) and press Enter. Excel will display the serial number for the date.

Google Sheets Example

The procedure in Google Sheets mirrors that of Excel. If you input the same date (“01/15/2022”) in text format in cell A1, the DATEVALUE function can be applied as follows:

A	B
01/15/2022	=DATEVALUE(A1)

Insert the formula =DATEVALUE(A1) in cell B1 and press Enter. Google Sheets will then convert this text date into a serial number.

Use Case

A common application of the DATEVALUE function occurs when you have a dataset containing dates in text format and you need to conduct operations based on these dates. Converting your text dates into serial numbers allows you to sort, filter, and manipulate your data more conveniently.

In summary, the DATEVALUE function is an essential tool for converting text representations of dates into serial numbers, which simplifies the processing of date-related data in both Excel and Google Sheets.

More information: https://support.microsoft.com/en-us/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252

Other functions

DATE

Returns the serial number of a particular date

DATEDIF

Calculates the number of days, months, or years between two dates This function is useful in formulas where you need to calculate an age

DAY

Converts a serial number to a day of the month

DAYS

Returns the number of days between two dates

DAYS360

Calculates the number of days between two dates based on a 360-day year

EDATE

Returns the serial number of the date that is the indicated number of months before or after the start date

EOMONTH

Returns the serial number of the last day of the month before or after a specified number of months

HOUR

Converts a serial number to an hour

ISOWEEKNUM

Returns the number of the ISO week number of the year for a given date

MINUTE

Converts a serial number to a minute

MONTH

Converts a serial number to a month

NETWORKDAYS

Returns the number of whole workdays between two dates

NETWORKDAYS.INTL

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days

NOW

Returns the serial number of the current date and time

SECOND

Converts a serial number to a second

TIME

Returns the serial number of a particular time

TIMEVALUE

Converts a time in the form of text to a serial number

TODAY

Returns the serial number of today's date

WEEKDAY

Converts a serial number to a day of the week

WEEKNUM

Converts a serial number to a number representing where the week falls numerically with a year

WORKDAY

Returns the serial number of the date before or after a specified number of workdays

WORKDAY.INTL

Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days

YEAR

Converts a serial number to a year

YEARFRAC

Returns the year fraction representing the number of whole days between start_date and end_date