Excel
Word
Outlook
Excel Expert
Power Pivot
PowerPoint
April 10, 2024
Hi
I have a file exported from a system and it has US dates which show as text (general) for some rows and dates for other rows depending on the day. if the day is under 10 it shows as a date, otherwise its text.
I want to be able to simply convert to AU dates. I have tried Text to Columns > Date (DMY) but it does nothing.
Also tried DateValue() but get #VALUE error.
as there are two types of data in the column I need to different formuals?
July 16, 2010
Hi Sam,
You should use Power Query to import the data and then you can use the 'Change Type > Using Locale' menu to correct the date format.
See Option 2 in this tutorial: Fix Excel Dates Formatted as Text • My Online Training Hub
And if you have any trouble, this one explains the Change Type > Using Locale menu.
Mynda
Excel
Word
Outlook
Excel Expert
Power Pivot
PowerPoint
April 10, 2024
Trusted Members
October 17, 2018
Hi Sam, dates are still a p in the A when it comes to dates.
My solution is to always try and convert any data or text that is a date value to a serial date (the format yyyymmdd), it will take some thingkinh especially when the day is 12 or smaller, that's when it fails.
Once you manage that then the issues (most of them at least) are less.
I use these UDFs
Public Function Date2Serial(Optional mydate As Date, Optional inclTime As Boolean = False) As Double
If mydate = 0 Then mydate = Date
Select Case inclTime
Case Is = False
Date2Serial = DateSerial(Year(mydate), Month(mydate), Day(mydate))
Case Is = True
Date2Serial = DateSerial(Year(mydate), Month(mydate), Day(mydate)) + time2Serial(mydate)
End Select
End Function
Public Function time2Serial(Optional tTime As Date) As Double
If tTime = 0 Then tTime = Now()
time2Serial = TimeSerial(Hour(tTime), Minute(tTime), Second(tTime))
End Function
Public Function serial_TimeStamp(Optional mydate As Variant) As Double
If Not IsDate(mydate) Then Exit Function
Dim xDate As Date
If mydate = 0 Then xDate = Now()
serial_TimeStamp = Date2Serial(xDate) + time2Serial(xDate)
End Function
Hopw it helsp some
Answers Post
1 Guest(s)