Solved

Excel date format display issue

Posted on 2015-01-12
5
183 Views
Last Modified: 2015-01-12
Hi,

Attached excel sheet I want to convert date format in Red columns into dd/mm/yyyy
file0.xlsx
0
Comment
Question by:a500505077
5 Comments
 
LVL 13

Expert Comment

by:akb
Comment Utility
The dates in the red columns are not really dates, they are numbers. You need to convert them to strings and then extract the required elements and convert them to dates.
The formula is:
=CONCATENATE(LEFT(TEXT(F2,"00000000"),2),"/",MID(TEXT(F2,"00000000"),3,2),"/",MID(TEXT(F2,"00000000"),5,4))
where the date is in cell F2.
See the attached file.

TEXT(F2,"00000000") converts the number to text and pads it on the left with zeros for the short dates.
file0.xlsx
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
Comment Utility
You can use this macro if the column headers are colored as in the file given by you

Sub txt2date()
    Dim cel As Range
    For Each col In Range("1:1")
    If col.Interior.Color = 255 Then
    For Each cel In col.EntireColumn.Cells
        'cel.Select
        If cel.Row > 1 Then
            If Trim(cel) <> "" Then
                cel = DateSerial(Right(cel, 4), Mid(cel, Len(cel) - 5, 2), Left(cel, Len(cel) - 6))
                cel.NumberFormat = "dd/mm/yyyy"
            End If
        End If
        Next cel
        End If
    Next col
End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
A shorter formula:

=DATE(RIGHT(B2,4),MID(B2,LEN(B2)-5,2),LEFT(B2,LEN(B2)-6))

Allows for the ddmmyyyy and the dmmyyyy formats.

Put the above formula into column AN and then copy and paste to columns AR, AZ, BE and BF, You can then copy this whole block (AN to BF) and paste values back onto the original data, select the skip blanks option so it doesn't overwrite the values of the columns between.

Thanks
Rob H
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
I assume that the columns aren't actually colored red when you want to run the macro. I therefore specified the columns to be converted in the following macro:
Sub DateConverter()
Dim v As Variant
Dim cel As Range, rg As Range
Application.ScreenUpdating = False
With ActiveSheet
    For Each v In Array("B", "F", "N", "S", "T")
        Set rg = Intersect(.Columns(v), .UsedRange)
        For Each cel In rg.Cells
            If IsNumeric(cel.Value) Then
                cel.Value = Format(cel.Value, "00\/00\/0000")
            End If
        Next
        rg.TextToColumns Destination:= _
            rg.Cells(1), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
        rg.NumberFormat = "dd/mm/yyyy"
    Next
End With
End Sub

Open in new window

0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
And if you want a formula solution and your default date format is dd/mm/yyyy:
=--TEXT(B2,"00\/00\/0000")
You will need to format the column as dd/mm/yyyy, as the values will otherwise appear to be numbers like 40384.

The above formula will not work in all cases if your default date format is mm/dd/yyyy. In particular, an ambiguous date like 03/05/2014 will be converted to March 5, 2014 rather than the desired 3 May 2014.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now