2

I have a service that spits database data out into CSV format, that I can't necessarily change right now.

While the executing query returns the standard SQL date format - 2014-12-26 19:41:23.012 - the Node.js middleware in between seems to convert the date into a Javascript standard date format:

new Date().toString();
// -> Fri Dec 26 2014 19:36:00 GMT-0800 (Pacific Standard Time)

To my dismay, Excel cannot recognize this as a date format, and I have to go at heavy lengths to chop it up into something that excel can recognize.

While i'm not too familiar with it, I know the Custom format cell property can help in date manipulation.

What do I have to do to get Excel to recognize this date format?

Improve this question
3
  • is "Fri Dec 26 2014 19:36:00 GMT-0800 (Pacific Standard Time)" literally the way Excel receives it? Is all of that a single CSV field? Also, are you in PST?
    – fixer1234
    Commented Dec 27, 2014 at 4:39
  • Yes on both of the above.
    – dthree
    Commented Dec 27, 2014 at 5:26
  • JavaScript date format is something like this: 2022-09-27T14:44:09.2191249Z, not what you have above.
    – tom redfern
    Commented Sep 28, 2022 at 11:02

4 Answers 4

Reset to default
2

I had almost the exact problem 2 days ago. The date was in a text format, and excel couldn't convert it without some manipulation. Here's what I had to do to your string to get it to work (using Excel 2010):

  1. Strip out the 'Fri' in the beginning and the 'GMT..." at the end. If the text is in A1, then B1 is:

=MID(A1,4,LEN(A1)-35

  1. Add a comma between the day and the year. So column C1 is:

=LEFT(B1, 7) & "," & MID(B1,8,LEN(B1))

  1. Convert to date/time. Column D1:

=DATEVALUE(C1)+TIMEVALUE(C1)

Depending on where your spaces are, you might need to fool around with the string indices. And you'll still need to use the custom format to change the display to what you want.

You could also combine this into one long formula:

=DATEVALUE(LEFT(MID(A1,4,LEN(A1)-35), 7) & "," & MID(MID(A1,4,LEN(A1)-35),8,LEN(MID(A1,4,LEN(A1)-35))))+TIMEVALUE(LEFT(MID(A1,4,LEN(A1)-35), 7) & "," & MID(MID(A1,4,LEN(A1)-35),8,LEN(MID(A1,4,LEN(A1)-35))))

Improve this answer
0

The Custom format only display the text or number in desired format. But the base data should be recognized first by excel after that Custom format will work.

In your case you need to first get the 25 chars from left in Excel.

=LEFT(A1,25)

Then paste as values and after that apply the Date format and Excel will recognize it.

Improve this answer
3
  • 1
    You beat me to it. I was just testing that in LibreOffice Calc. Everything starting with GMT is unnecessary unless you are in a different time zone (in which case you can just build in a permanent adjustment, you don't need to decipher and calculate an adjustment for every record). The "Fri" is a derived value, which isn't needed (you could use MID() instead of LEFT() to chop it off), but Excel and Calc ignore it. Once you save it as a date/time, you can format or use it as needed.
    – fixer1234
    Commented Dec 27, 2014 at 4:58
  • Thanks and I had tried this (tried again), but Excel is still not recognizing it as a date. An exact example is: Sun Nov 02 2014 21:10:27
    – dthree
    Commented Dec 27, 2014 at 5:27
  • Sun Nov 02 2014 21:10:27 then that must be formatted as text currently. You need to first convert it to number then apply the date format and excel will recognize it. For that you need to multiply the value with 1. Like =A1*1 Then change it to date format. Hope that helps.
    – Surya
    Commented Dec 27, 2014 at 5:37
0

Assuming cell A1 has a value of "Fri Dec 26 2014 19:36:00 GMT-0800 (Pacific Standard Time)" then use the following formula for correct date output

=DATEVALUE(MID(A1,9,2)&MID(A1,5,3)&MID(A1,12,4))
Improve this answer
1
  • What about the time component of the datetime?
    – tom redfern
    Commented Sep 28, 2022 at 11:00
-1

To convert JS new Date() format to something readable by Excel you can use the following steps:

  1. Get the first 10 digits of the date:

2019-11-19T14:15:05.000Z -> =left(A1, 10) = 2019-11-19

  1. Convert the result value to number:

2019-11-19T14:15:05.000Z -> =left(A1, 10)*1 = 43788

  1. Change the format of the cell from General to Short Date or Long Date:

2019-11-19T14:15:05.000Z -> =left(A1, 10)*1 = 43788 -> Change to Long Date Format = Tuesday, November 19, 2019

Improve this answer
1
  • What about the time component of the datetime?
    – tom redfern
    Commented Sep 28, 2022 at 11:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .