LinkBack URL
About LinkBacks
I have a date field in a worksheet where the dates are being exported as text into excel. See below.
?*
Aug 28, 2024 05:00 PM (PST)
I need to convert the text to a date. Is there a formula to convert the below to a Date in excel?
I have a date field in a worksheet where the dates are being exported as text into excel. See below.
?*
Aug 28, 2024 05:00 PM (PST)
I need to convert the text to a date. Is there a formula to convert the below to a Date in excel?
Still using Excel 2021?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Yes excel 2021 but also have access to Office 365.
The dates in the file are different in two columns.
Tue, Sep 03, 2024 10:31 AM (Using this formula to convert this one =DATEVALUE(MID(A3,10,2)&"-"&MID(A3,6,3)&"-"&MID(A3,14,4)) it is not converting the one below.
Aug 28, 2024 05:00 PM (PST)
Thank you!
Power Query
Please Login or Register to view this content.
Thank you!
If those really are the only formats, maybe:
Formula:
Please Login or Register to view this content.
I had no trouble using the DATEVALUE() function coupled with LEFT() function to remove the time zone information.
1) LEFT(A3,LEN(A3)-6). Here I am assuming that the time zone text will always be 5 characters plus an extra character to get rid of the space between the time and the time zone. If the text strings are not that consistent, then additional computation would be needed to correctly remove the time zone information.
2) The DATEVALUE() function can then convert the text to number =DATEVALUE(LEFT(A1,LEN(A1)-6)). The DATEVALUE() function uses the default formats in your OS regional and language settings to decide what text it can understand. My OS settings match fairly well with mmm dd, yyyy hh:mm PM, so my copy of Excel has no difficulty with this text.
Does that help?
Originally Posted by shg
Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks