Converting cell value from general to date

Nelson78

Well-known Member
Joined
Sep 11, 2017
Messages
525
Office Version
  1. 2007
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.
 
I'm tinkering about, but it's a tough nut to crack...
Not really, you're just making it tougher than it needs to be.

Applying the format code from post 8 to the result that you had in post 4 is a very simple task.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
The below illustrates the formula solution and replicates it with VBA.



Date and Time.xlsm
ABC
1TextDate
201-10-2021 10:1101-Oct-2021 10:11 formatted dd-mmm-yyyy hh:mm
3
4
501-10-2021 10:1101-Oct-2021 10:11
23a
Cell Formulas
RangeFormula
B2,B5B2=A2+0


VBA Code:
With Range("B5")
    .FormulaR1C1 = "=RC[-1]+0"
   .NumberFormat = "dd/mmm/yyyy hh:mm"
   End With
 
Upvote 0
ps. I need a vba solution.
For a bad text date if formatted like the Regional Settings so just using the FormulaLocal property like ActiveCell.FormulaLocal = ActiveCell.Value2 …​
But check first if the cell is formatted as text then in this case you need to format first the cell as a date like ActiveCell.NumberFormat ="m/d/yyyy h:mm" …​
 
Last edited:
Upvote 0
I revised Marc L 's suggestion to consider my system settings and preferences
Marc L's suggestion also works if the text is like '01-10-2021 6:11 PM

VBA Code:
Sub TextToDateTimeIntn()
ActiveCell.NumberFormat = "dd/mmm/yyyy hh:mm"
ActiveCell.FormulaLocal = ActiveCell.Value2
End Sub

Column A initially had the text shown in Column B
Date and Time.xlsm
ABC
10Initially Text
1101-Oct-2021 10:1101-10-2021 10:11
1201-Oct-2021 18:1101-10-2021 18:11
1301-Oct-2021 18:1101-10-2021 6:11 PM
14
23a
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top