Converting cell value from general to date

[Nelson78]

Hello everybody.

I have a software providing the dates in general format.
For instance, in a cell I can read the following value:

01-10-2021 10:11

meaning 1st October 2021 h 10:11 am.

I need to convert this general format in date format.

How can I figure it out?

Thank's in advance.

 

[jasonb75]

Sounds like it's a text date that needs coercing, if it was a valid date then it would be showing as a custom format rather than general. Try copying an empty cell, then PasteSpecial to the date cell, choose Values and Add from the options before clicking OK.

 

[Marc L]

Hello,​

​

what about just replacing dash with slash ?​

 

[Nelson78]

The result is:

44470,42639

And then?

ps. I need a vba solution.

 

[Joe4]

The result is:

44470,42639

That is actually correct. That is just your date in "General" format. If you change it to your desired Date/Time format, you will see it the way you are used to.

The reason for this is that Excel actually stores dates and times as numbers, specifically the number of days since 1/0/1900. So time is just the fraction (decimal) of one day.
If you enter a date in any cell, and change the format of the cell to "General", you will see it as Excel does, which looks like the large number you posted.

 

[Nelson78]

Yes, I tried to convert with the following operation (cell is the value in the cell):

cell = Format(cell, "dd/mm/yyyy hh:mm")

But something gets wrong.

For instance:

01-10-2021 10:11

is converted in
10th January 2021, not in 1st October 2021

 

[Joe4]

Yes, I tried to convert with the following operation (cell is the value in the cell):

cell = Format(cell, "dd/mm/yyyy hh:mm")

But something gets wrong.

For instance:

01-10-2021 10:11

is converted in
10th January 2021, not in 1st October 2021

That is because VBA does NOT use your Regional Date Settings, and always uses the American date format.
You may need to explicitly define each component of the date, using the DateSerial function in VBA (VBA Dates & Times - Functions) and then adding the time to it.

 

[jasonb75]

You need to format the cell, not the value that is in the cell. The way that you are trying to do it will most likely still leave you with a text string, not a valid date.

cell.NumberFormat = "dd/mm/yyyy hh:mm"

 

[Nelson78]

That is because VBA does NOT use your Regional Date Settings, and always uses the American date format.
You may need to explicitly define each component of the date, using the DateSerial function in VBA (VBA Dates & Times - Functions) and then adding the time to it.

I'm tinkering about, but it's a tough nut to crack...

 

[Joe4]

The tough part for me is that since I am using the American version myself, it is hard for me to replicate your exact scenario (so what works for me may not work for you).

My thought was since your value is a string, you can use LEFT and MID functions along with DateSerial to build the date you need, something like:

dte = DateSerial(Mid(cell,7,4),Mid(cell,4,2),Left(cell,2))