Date Functions – Using Dates and Times in Google Sheets

The data used in spreadsheets is often a combination of numbers, text, dates, and other data types. Each different data needs to be treated differently inside functions.

Dates require their own set of specialized functions. They work differently than regular numbers requiring the user to take more care when using them in formulas. You can sort them into chronological order or subtract two points in time to find age or duration. You can also use the difference between days as multipliers in other calculations, such as determining interest on a bond. Date functions such as DAYS, MONTH, or WEEKDAY to name a few, deal with issues that are specific to dates such as extracting only the month number or returning the number of days in a month.

Date Validity

ℹ️ The easiest way to write a valid date is to use slashes such as 10/25/1999 instead of spelling out October 25, 1999.

Dates and times in Google Sheets work differently than numbers. Spreadsheets need help to figure out if a value is a date or time instead of just a string of other characters. One key to getting your spreadsheet to recognize date values is using the correct syntax. To see if a date is valid is to run it through the ISDATE function. The ISDATE function will return a TRUE if the date is valid or a false if it is not.

Conditional Formatting

Conditional formatting is another method to determine if a date is valid. You can apply a rule that highlights cells if they contain valid dates using the Custom formula is option and using the ISDATE function.

Data Validation

Lastly, you can enforce the propriety of dates using data validation.

You can use data validation to force users to enter valid dates or give them a warning. This option can be found in the Data menu. Data validation can be useful when collaborating with several users.