How to change string to date and time

[aliyyah]

I cant change my string to date and time.
ERROR String to Date&Time 0:27 Execute failed: Failed to parse date in row '1: Text ‘2/2/2015 14:19’ could not be parsed at index 0

[ScottF]

Hi @aliyyah and welcome to the forum.

For your date format, try the following:

M/d/yyyy HH:mm

It’s possible you may need to switch the month and day identifiers depending on the rest of your data.

[aliyyah]

Hi! I tried following the date format but it failed.
ERROR String to Date&Time 0:29 Execute failed: Failed to parse date in row '582: Text ‘3/2/2015 0:00’ could not be parsed at index 9

Is there another way I can change the string to date and time?

[takbb]

Hi @aliyyah,

The answer that @ScottF gave was right based on the format of the specific date time in your first post, which had a two 2 digit hour, but your later message, indicated that this failed for the data item on row 582 which has a single digit hour for midnight.

Therefore you need the slightly more generic format

M/d/yyyy H:mm

This assumes that the two dates we’ve seen represent the following:

As Scott mentioned, we are having to make assumptions here about the ordering of your Month and Day values as we cannot know from the data.

Given that the two data items it has failed on are
2/2/2015 14:19
3/2/2015 0:00

… it now looks to me that it is likely (although I still cannot know for sure) that your dates are actually in day/month order rather than month/day, and so represent the following:

If that is the case, use this:

d/M/yyyy H:mm

[aliyyah]

Thank you so much! it worked