Home

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

Meilani Kelley
Occasional Visitor

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.

2 Replies
Can we have the sample of those non-standard date?
Highlighted

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies