CODEWITHSUNDEEP
excelexcel-formula
user3501194
user3501194

Reputation: 395

Convert date from d/M/yy hh:mm tt To dd/MM/yyyy HH:mm

How can I convert a date in excel from 1/4/19 1:00 PM to 01/04/2019 13:00.

The date is stored as a string since the file is exported from an application as an XLS ( There is no way to modify the source )

Upvotes: 0

Views: 942

Answers (2)

Forward Ed
Forward Ed

Reputation: 9874

Below is an image of the workflow to to convert your date from one format to another. It wraps up by substituting all the helper cells into one formula in the end. hard to read but does the job.

Answer

Understanding Excel's Date and Time

Date

Excel stores the date as an integer. It is the count of the number of days since January 0, 1900. So 1 is January 1st 1900. (I believe on Mac's the reference year is 1905 but I could be wrong). So all dates in excel are simply integers with some special formatting applied to display them in some manner we are used to seeing. You can test this out by typing 1 in an empty cell and then change the cells format from Date toe General. So if you need to just find the date from a mixed time date, you need to strip off just the integer portion.

TIME

Time is stored a % of a day or if you prefer decimal of a day. 0.5 represents half a day or lunch. Again this can be tested by entering 0.5 in an empty cell and then changing the cell format from General to Time. If you just need the time, you just need to strip of the decimal portion.

Combined Date and Time

Based on the above, you know the date is an integer, and time is the decimal. In order to combine the two, they simply need to be added together. In C21, just the date is determined. In C22, just the time is determined. In C23, the two elements are simply added together and a custom cell format of YY/MM/DD HH:MM is applied. An important difference between C23 and C28, is that C23 is a number and C28 is TEXT.

Upvotes: 2

Cyril
Cyril

Reputation: 6829

If i have a date that is displayed as a string, i typically use TextToColumns to have it recognize the date, then apply formatting as desired. The code I run is per Column: 

Columns("D").TextToColumns DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False

Note that I don't use any delimiters.

Then the formatting is something like:

Columns("D").NumberFormat = "MM/dd/yyyy HH:mm""

Edit1:

General subroutine format looks like:

Sub NameofMacro()
    Columns("D").TextToColumns DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False
    Columns("D").NumberFormat = "MM/dd/yyyy HH:mm""
End Sub

Upvotes: 0

Related Questions