To convert recognized date text in Google Sheets, enter =DATEVALUE(A2) in a helper column, check that the result is the intended date, then format it with Format > Number > Date. If Sheets does not recognize the text—or the day and month could be reversed—use the source’s known date order to build a date from its numeric parts instead. Formatting changes a value’s appearance; it does not reliably parse arbitrary text.
Choose the right conversion method
The key distinction is whether you need a real date value for sorting and calculations, or text that merely looks like a date. These functions do different jobs:
| Function | Best input | Result | Main caution |
|---|---|---|---|
DATEVALUE |
Text containing a date in a format Sheets recognizes | A date value stored as a number | Recognized formats can depend on spreadsheet region and language settings; the input must be text. Google Sheets DATEVALUE reference. |
VALUE |
Text in a date, time, or number format Sheets understands | A number, including when the input represents a date | Unsupported text returns an error, and successful parsing does not establish that an ambiguous date was interpreted as intended. Google Sheets VALUE reference. |
DATE |
Separate numeric year, month, and day components | A date value | Components must be numeric. Out-of-range month or day values are normalized into another date, so this is not input validation. Google Sheets DATE reference. |
TEXT |
An existing date or number value | Text in a chosen display pattern | The output is text, not a date value for date arithmetic. Google Sheets TEXT reference. |
Convert a date string Sheets recognizes
- Keep the source text in its original column and enter
=DATEVALUE(A2)in an empty helper column. - Compare the result with the source and confirm it represents the intended calendar date.
- Format the converted cells with Format > Number > Date. For a particular display, choose Format > Number > Custom date and time and select a pattern.
DATEVALUE parses recognized date text into a date value; the number format determines how that value appears. Google notes that some date strings are not understood and that interpretation can depend on the spreadsheet’s region and language settings. See Google’s DATEVALUE guidance.
Resolve ambiguous dates before converting
A string such as 03/04/2026 does not reveal by itself whether the date means March 4 or April 3. Confirm the convention used by the data source before applying a formula or changing spreadsheet settings. The same caution applies to imported strings with unfamiliar separators or ordering.
#1 Best Overall
- 【Google Sheet Shortcut】The Large mouse pad with shortcuts specifically designed for Google Sheets, making it easy for you to use Google Docs and improve work efficiency.
- 【HD Printing】Printed with high-tech precision for vibrant colors and sharp details, this mouse pad provides quick access to essential functions—an ideal addition to any workspace
- 【High Quality】Crafted from smooth microfiber cloth, this large gaming mouse pad offers a comfortable surface with reinforced stitched edges to prevent fraying. Its 3mm thickness ensures long-lasting durability
- 【Perfect Fit】Measuring 31.5 x 15.7 inches, this mouse pad offers ample space for your keyboard, mouse, and other accessories—perfect for both work and gaming
- 【Easy Maintain】Simply wipe with a damp cloth to keep your workspace clean and tidy
Google’s documentation says recognized formats can vary with region and language settings. A community discussion illustrates how changing a spreadsheet’s locale may change interpretation of day/month strings; it is user-contributed advice, not an official conversion procedure. Preserve the original text and verify known examples before relying on a locale change. Google Sheets community discussion about date formats.
Build a date when parsing fails
If the date’s order is known but DATEVALUE does not recognize its format, extract numeric year, month, and day components, then pass them to DATE. For example, if those components are already in cells, use =DATE(year_cell,month_cell,day_cell). The order matters: DATE takes year first, then month, then day.
Rank #2
- 【Google Shortcut Keys Mouse Pad 】- Extended Large Keyboard Shortcuts for Google Sheets, Mac Shortcuts,Window Spreadsheet Shortcuts Keys Shortcuts Gaming Keyboard Mouse Pad Mousepad Desk Mat
- 【HD Printing】Printed with high-tech precision for vibrant colors and sharp details, this mouse pad provides quick access to essential functions—an ideal addition to any workspace
- 【High Quality】Crafted from smooth microfiber cloth, this large gaming mouse pad offers a comfortable surface with reinforced stitched edges to prevent fraying. Its 3mm thickness ensures long-lasting durability
- 【Perfect Fit】Measuring 31.5 x 15.7 inches, this mouse pad offers ample space for your keyboard, mouse, and other accessories—perfect for both work and gaming
- 【Easy Maintain】Simply wipe with a damp cloth to keep your workspace clean and tidy
Google documents that DATE uses a date system counting days from December 30, 1899; it truncates decimal components and normalizes out-of-range month or day values. Check the extracted components against the source rather than treating a returned date as proof that the input was valid. Google Sheets DATE reference.
Convert a consistently structured column
For data with a consistent, understood structure, a formula can apply the conversion across a range. A Google Sheets Editors Community example for dot-separated dates is:
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteRank #3
- Google SketchUp - New Color Keyboard Shortcut Sticker (keys 11.5x13 mm)
- Keyboard Sticker Shortcut for Google SketchUp are laminated and made with typographical method on high-quality Matt Vinyl using non-toxic materials. Thickness - 80mkn. Made in USA.
- High quality sticker for keyboard! Once you apply the stickers, you can start editing right away.Stickers help all types of users, from beginner to professional.
- Shortcut will help improve your productivity by 15-40%, saving you time, while helping you enjoy your work
- Keyboard Shortcut Google SketchUp . KEYBOARD NOT INCLUDED
=ArrayFormula(if(A2:A="",,VALUE(SUBSTITUTE(A2:A,".","/"))))
It replaces periods with slashes, passes the resulting strings to VALUE, and leaves blank source rows blank. The example is community-contributed, not an official Google recipe. It is appropriate only when the data’s date order is known and the replacement produces strings Sheets interprets as intended. Adjust blank and error handling to your sheet, format the results as dates, and inspect sample rows before using the formula on the full column. Google Sheets Editors Community example.
Rank #4
- 【Google Shortcut Keys Mouse Pad 】- Extended Large Keyboard Shortcuts for Google Sheets, Mac Shortcuts,Window Spreadsheet Shortcuts Keys Shortcuts Gaming Keyboard Mouse Pad Mousepad Desk Mat
- 【HD Printing】Printed with high-tech precision for vibrant colors and sharp details, this mouse pad provides quick access to essential functions—an ideal addition to any workspace
- 【High Quality】Crafted from smooth microfiber cloth, this large gaming mouse pad offers a comfortable surface with reinforced stitched edges to prevent fraying. Its 3mm thickness ensures long-lasting durability
- 【Perfect Fit】Measuring 31.5 x 15.7 inches, this mouse pad offers ample space for your keyboard, mouse, and other accessories—perfect for both work and gaming
- 【Easy Maintain】Simply wipe with a damp cloth to keep your workspace clean and tidy
Check the results and troubleshoot errors
#VALUE!fromDATEVALUE: The input may not be text, or its format, separator, or order may not be recognized. Check the source convention and spreadsheet locale, then test a representative string in an empty cell. Google Sheets DATEVALUE reference.- Changing the cell format did not convert it: A date number format controls display; it does not itself parse arbitrary text. Convert the text with
DATEVALUE,VALUE, orDATEfirst, then format the result. - The result looks plausible but may be reversed: Verify dates where day and month are both 12 or lower against the source convention, and check entries near month and year boundaries. The string alone cannot settle an ambiguous order.
- Dates do not sort or calculate as expected: Check that the converted cells are numeric date values rather than text that only looks like a date. Keep the original column until the converted values have been verified.
Display a date as text only when needed
If you already have a valid date value and want a fixed text representation, use =TEXT(B2,"yyyy-mm-dd"). Google supports patterns such as d, dd, mmm, mmmm, yy, and yyyy. Because TEXT returns text, use it for presentation or exports where a fixed string is wanted—not as the conversion step for date calculations. Google Sheets TEXT reference.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Recommended Free Tools
