Forum Discussion

Meilani Kelley's avatar
Meilani Kelley
Copper Contributor
Feb 13, 2018

How do I convert dates such as '01-NOV-17 05.35.07 PM' to a standard date format?

I need to be able to quickly sort and filter a very large file, but I can't since the dates are in a non-standard format. How can I quickly change the format to a '01-NOV-17' date? It would take hours to change the several thousands of line items one by one.

Formulas & Functions
tips and tricks

2 Replies

Sort By
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Feb 13, 2018

    Meilani,

     

    These dates are treated as texts so you have to convert them to real dates by using this formula:

    =DATEVALUE(SUBSTITUTE(A1,".",":"))

     

    This formula may give you the serial number of the date!

    No problem, you can format these numbers to date format as follows:

     

    Highlight them, press Ctrl+1, go to Date category, select the format that you prefer, and then hit OK.

Resources