Automatically convert date text to correct date format using Google Sheets
I'm trying to convert date from "text" to correct format. It is logged to Google Spreadsheets and I'm unable to use it to plot graphs.
This is the text format: February 3, 2018, at 11:21 AM
Time is not relevant, all I need is the date converted: DD/MM/YYYY.
I found a similar question where Gary's Student answered with a formula that looks like this for a different format:
=DATEVALUE(SUBSTITUTE(A1,MID(A1,FIND(" ",A1)-2,2),""))
How can I use above formula (or something similar) so that text is converted to date?
Thanks in advance.
The , at portion of the string is keeping Google Sheets from recognizing it as a datevalue. Just remove it with the substitute function and wrap in datevalue function like so: =DATEVALUE(SUBSTITUTE(A1,", at",""))
To format as DD/MM/YYYY just go to custom formatting and set it to look like the following:
- GWT get build date
- PHP date() with timezone?
- Comparing text fields that contain dates in Google Sheets
- Detect daylight saving time in bash
- java.util.Date vs java.sql.Date
- Find all month names between two given date in java
- vba return first row number in column that has todays date or nearest date after today
- Iterate over all last-day-of-the-month dates in a year
- How to set the current date in the gnuplot title
- How can I validate a date in Python 3.x?
- how to use str_to_date (MySQL) to parse two-digit year correctly
- Jquery dd/MM/yyyy date format validation
- Duration between two dates in Ruby (Rails)
- How to generate a Date from just Month and Year in Java?
- In Swift 5, how do I increment a date property by some interval and then run the function with that new future date?
- Last day of quarter
- Split timeperiods by referring dates in another table
- for loop to generate time list in javascript
- Implementing range between dates in a window
- How to calculate difference between dates by group in SAS
- Why does DateTime.Now to the yyyy-mm-dd format give me incorrect month results?
- How to get current date and time in WordPress?
- Date change function in PHP
- Best approach to remove time part of datetime in SQL Server
- How to get year/month/day from a date object?
- How to update a field in React Hook Form based on other fields' values?
- How to check if there is a weekend between two dates in php?
- Get next day, skip weekends
- Check if one date is between two dates
- Trying to figure out why my GoogleSheets Date formula is not working