• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

Excel number to date

Hi,
I've looked a lot but cannot find solution to match my query.
I get data sent to me each week, but when converted to excel, the dates end up being this format nn/nn.
So for instance 29th April would be 29/04, but my formulas don't see 29/04 as a date.
Is there a way I can convert this string to a date using vba? The dates are all in row 1 by the way.
0
Stephen Byrom
Asked:
Stephen Byrom
  • 3
  • 3
1 Solution
 
Saurabh Singh TeotiaCommented:
When you say all dates are in row-1..If i understand that correctly you meant to say dates are in a1,b1,c1 or you meant to say dates are in a1,a2,a3??

Saurabh...
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Hi,
All dates are in row 1.
i.e. a1 b1 c1 etc etc
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Basic question: What is really stored in those cells? Are it dates formatted nn/nn, or the string? If you click on a cell, what does it display in the formular bar above the sheet?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Stephen ByromWarehouse/ShippingAuthor Commented:
it displays 30/04, for example
0
 
Saurabh Singh TeotiaCommented:
You can use the following code it will do what you are looking for..

Sub convertdates()
    Dim rng As Range, cell As Range
    Dim lcol As Long

    lcol = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
    Set rng = Range("A1:" & Cells(1, lcol).Address)

    For Each cell In rng

        cell.TextToColumns Destination:=cell, DataType:=xlDelimited, FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
    Next cell

End Sub

Open in new window

0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Perfect!!
Thank you for your time
0
 
Saurabh Singh TeotiaCommented:
Yw Stephen..  :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now