Convert a Text String Date to Date Serial Number

**herve73** · 11-06-2015, 07:57 AM

Hi,
I run reports where I need to sort the Data by Due date, but the raw data that i get does not give me a date in a format that excel recognize,
it comes up as follow:
10-Apr-2015 11:59:59 PM UTC-04:00
I cannot format this as a date, Excel does not recognize it
Does anyone knows of a formula that would allow me to transform this to a date,
any help, greatly appreciated, thanks

Hervé

**JohnTopley** · 11-06-2015, 08:04 AM

try ..

=DATEVALUE(LEFT(A1,20))

to give you the date

OR

=TEXT(LEFT(A1,20),"dd/mm/yyyy hh:mm:ss")

**herve73** · 11-10-2015, 10:38 AM

Hi,

Thanks, this worked, both solution are good.

Sorry not to get back to you earlier, I was out of work, and did not turn my PC on for a few days.

Thanks for the quick response
Hervé

**JohnTopley** · 11-10-2015, 10:53 AM

Thanks for the feedback.

Can you please mark thread as SOLVED ("Thread Tools" at top of first post)

Convert a Text String Date to Date Serial Number

LinkBack

Thread Tools

Rate This Thread

Display

Convert a Text String Date to Date Serial Number

Re: Convert a Text String Date to Date Serial Number

Re: Convert a Text String Date to Date Serial Number

Re: Convert a Text String Date to Date Serial Number

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Convert date string to serial number

how to convert date serial number to regular date?

Convert date and time in serial number format to text format

Converting a date serial number back to a date within a string

[SOLVED] Extract fraction from text without converting to date serial number

Convert Serial Number to Date WITH TEXT

[SOLVED] Date to TEXT. NOT to sequential serial number

How do I convert Date serial number to date

Bookmarks

Bookmarks

Posting Permissions