Converting a non-standard date string to a date

Question

I have a spreadsheet containing 100k dates in the following format:

Thursday 29th of October 2015 01:06:21 PM

I need to convert these into a useable date format. YYYY/MM/DD, or anything will do. As long as I can get it into a standard format.

I've looked at various PHP/JS date functions, but this date doesn't appear to be recognised. I assume it could be done with some Regex, and I was wondering if anyone has been here before?

This could be even more easily done in excel btw. Unless you need to automate it a truckload on various other spreadsheets. — WouldBeNerd, Sep 03 '16 at 08:56

score 1 · Answer 1 · answered Sep 03 '16 at 09:42

1

You could just replace 'of ' with an empty string and use Date for it.

var dateString = 'Thursday 29th of October 2015 01:06:21 PM'.replace('of ', ''),
    date = new Date(dateString);

console.log(date);

answered Sep 03 '16 at 09:42

Nina Scholz

376,160
25
347
392

This also requires that the 'th' be removed, but thanks, it certainly works. – Chris J Allen Sep 03 '16 at 10:27

score 0 · Answer 2 · answered Sep 03 '16 at 08:40

This code will surely helpful for..

    var DateObj = new Date()

    function generateDateTOYYMMDD(DateObj){
        var DateDay = DateObj.getDate();
        DateDay = (DateDay<10) ? "0"+DateDay : DateDay;
        var DateMonth = DateObj.getMonth() + 1;
        DateMonth = (DateMonth<10) ? "0"+DateMonth : DateMonth;
        var DateYear = DateObj.getFullYear();
        var DateStr = DateYear + "-" + DateMonth+ "-" + DateDay;
        return DateStr;
    }

score 0 · Answer 3 · answered Sep 03 '16 at 08:52

You could do everything with string.split() and array.indedOf(); Some Examples.

var dateString = "Thursday 29th of October 2015 01:06:21 PM";
var parts = [];
parts = dateString.split(" ");
console.log(parts);

var months = ["January", "February","March"];
var monthInt = months.indexOf("March");
console.log(monthInt);

after that you just convert to Date

var newDate = new Date();
newDate.setMonth(3);
newDate.setDate(15);
newDate.setYear(2016);
console.log(newDate)

special note about month, it counts 0-11 not 1-12 – WouldBeNerd Sep 03 '16 at 08:53 — WouldBeNerd, Sep 03 '16 at 08:53

Yogesh Singasane · Answer 4 · 2016-09-03T09:55:56.127

0

In PHP, DateTime::createFromFormat — Returns new DateTime object formatted according to the specified format

  $str_date = 'Thursday 29th of October 2015 01:06:21 PM';
    $obj_date = DateTime::createFromFormat('l jS \of F Y h:i:s A', $str_date);
    echo date('Y/m/d',$obj_date->getTimestamp());

edited Sep 03 '16 at 09:55

answered Sep 03 '16 at 09:11

Yogesh Singasane

283
3
12

Please provide more details to your answer as this post has been found in low quality post. Code only and 'try this' answers are discouraged as it doesn't provide any searchable content and why people should 'try this'. – Paritosh Sep 03 '16 at 09:48

Converting a non-standard date string to a date

4 Answers4