Solved

# Excel date format display issue

Posted on 2015-01-12
Medium Priority
224 Views
Hi,

Attached excel sheet I want to convert date format in Red columns into dd/mm/yyyy
file0.xlsx
0
Question by:a500505077
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 13

Expert Comment

ID: 40544040
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

Saqib Husain, Syed earned 2000 total points
ID: 40544069
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
0

LVL 33

Expert Comment

ID: 40544232
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 81

Expert Comment

ID: 40544828
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
0

LVL 81

Expert Comment

ID: 40544845
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month15 days, 2 hours left to enroll