Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Date formats in text to be recognised as date value
Hi all,
I have a problem with some date formats where people send to me in various forms (text format) 1) "DD\MM\YYYY" 2) "D\MM\YYYY" 3) "D\M\YYYY" 4) "MM\DD\YYYY" 5) "MM\D\YYYY" 6) "M\D\YYYY" Is there anyway to be able to convert these to date values and be reflected as "DD\MM\YYY" using vba. Thank you for any help rendered in advance. Rgds Ray |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Date formats in text to be recognised as date value
There is a problem with internation formats. Excel won't convert US (month -
day) to England (day - month) standard or the other way. within one country you can easily convert using format(DateValue("1/1/09"),"DD/MM/YYY") "swiftcode" wrote: Hi all, I have a problem with some date formats where people send to me in various forms (text format) 1) "DD\MM\YYYY" 2) "D\MM\YYYY" 3) "D\M\YYYY" 4) "MM\DD\YYYY" 5) "MM\D\YYYY" 6) "M\D\YYYY" Is there anyway to be able to convert these to date values and be reflected as "DD\MM\YYY" using vba. Thank you for any help rendered in advance. Rgds Ray |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Date formats in text to be recognised as date value
You need more information. For example 3\7\2009 might mean
March 7 2009 in format #6 or July 3 2009 in format #3 -- Gary''s Student - gsnu200907 "swiftcode" wrote: Hi all, I have a problem with some date formats where people send to me in various forms (text format) 1) "DD\MM\YYYY" 2) "D\MM\YYYY" 3) "D\M\YYYY" 4) "MM\DD\YYYY" 5) "MM\D\YYYY" 6) "M\D\YYYY" Is there anyway to be able to convert these to date values and be reflected as "DD\MM\YYY" using vba. Thank you for any help rendered in advance. Rgds Ray |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Date formats in text to be recognised as date value
Hi Gary and Joel,
Thanks. You are both right. This is a problem for me when people from different countries send me information using their preferred formats, as i need the information in date value and in British format. On the part regarding more information, usually when people send me their data, i assume that it is eithier American or British, so if it is "DD\MM\YYYY" or "D\M\YYYY" i will take it as British and if it is "MM\DD\YYYY" or "M\D\YYYY" as American. Would you fellas be able to help me on this? Thank you. Rgds Ray "Gary''s Student" wrote: You need more information. For example 3\7\2009 might mean March 7 2009 in format #6 or July 3 2009 in format #3 -- Gary''s Student - gsnu200907 "swiftcode" wrote: Hi all, I have a problem with some date formats where people send to me in various forms (text format) 1) "DD\MM\YYYY" 2) "D\MM\YYYY" 3) "D\M\YYYY" 4) "MM\DD\YYYY" 5) "MM\D\YYYY" 6) "M\D\YYYY" Is there anyway to be able to convert these to date values and be reflected as "DD\MM\YYY" using vba. Thank you for any help rendered in advance. Rgds Ray |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Date formats in text to be recognised as date value
Read Gary''s Student's reply again... how will you know which format a date
like 3/7/2009 was originally in (D/M/YYYY or M/D/YYYY)? -- Rick (MVP - Excel) "swiftcode" wrote in message ... Hi Gary and Joel, Thanks. You are both right. This is a problem for me when people from different countries send me information using their preferred formats, as i need the information in date value and in British format. On the part regarding more information, usually when people send me their data, i assume that it is eithier American or British, so if it is "DD\MM\YYYY" or "D\M\YYYY" i will take it as British and if it is "MM\DD\YYYY" or "M\D\YYYY" as American. Would you fellas be able to help me on this? Thank you. Rgds Ray "Gary''s Student" wrote: You need more information. For example 3\7\2009 might mean March 7 2009 in format #6 or July 3 2009 in format #3 -- Gary''s Student - gsnu200907 "swiftcode" wrote: Hi all, I have a problem with some date formats where people send to me in various forms (text format) 1) "DD\MM\YYYY" 2) "D\MM\YYYY" 3) "D\M\YYYY" 4) "MM\DD\YYYY" 5) "MM\D\YYYY" 6) "M\D\YYYY" Is there anyway to be able to convert these to date values and be reflected as "DD\MM\YYY" using vba. Thank you for any help rendered in advance. Rgds Ray |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Date formats in text to be recognised as date value
Try to take control of the material you are given. Ask for dates like:
18 January 2009 Why select an ambiguous format when non-ambiguous formats are available?? -- Gary''s Student - gsnu200907 "swiftcode" wrote: Hi Gary and Joel, Thanks. You are both right. This is a problem for me when people from different countries send me information using their preferred formats, as i need the information in date value and in British format. On the part regarding more information, usually when people send me their data, i assume that it is eithier American or British, so if it is "DD\MM\YYYY" or "D\M\YYYY" i will take it as British and if it is "MM\DD\YYYY" or "M\D\YYYY" as American. Would you fellas be able to help me on this? Thank you. Rgds Ray "Gary''s Student" wrote: You need more information. For example 3\7\2009 might mean March 7 2009 in format #6 or July 3 2009 in format #3 -- Gary''s Student - gsnu200907 "swiftcode" wrote: Hi all, I have a problem with some date formats where people send to me in various forms (text format) 1) "DD\MM\YYYY" 2) "D\MM\YYYY" 3) "D\M\YYYY" 4) "MM\DD\YYYY" 5) "MM\D\YYYY" 6) "M\D\YYYY" Is there anyway to be able to convert these to date values and be reflected as "DD\MM\YYY" using vba. Thank you for any help rendered in advance. Rgds Ray |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Date formats in text to be recognised as date value
Hi Rick & Gary,
I understand what was said. What i meant was the when people send me their information, usually there are a few hundred lines of data, and 1 column dedicated to the dates, which is generated from some program. As there are dates in the spreadsheets provided, will usually be in either (entirely) British or American format. hence once i identify that, then it is easier to determine whether dates like 3/7/2009 is either stated in (D/M/YYYY or M/D/YYYY), as there will ve other dates for me to eyeball and see the format e.g. 3/21/2009, which effectively tells me that it is "M/DD/YYYY". My apologies in not being able to better describe the senario better. It is not possible to take control and specifically ask for date formats to suit me as these data providers are not obligate to give me the information. Would anyone be able to help? My idea is that if i know its British format, then i will invoke 1 set of vbas, if its American format, another. Thanks Rgds Ray "Gary''s Student" wrote: Try to take control of the material you are given. Ask for dates like: 18 January 2009 Why select an ambiguous format when non-ambiguous formats are available?? -- Gary''s Student - gsnu200907 "swiftcode" wrote: Hi Gary and Joel, Thanks. You are both right. This is a problem for me when people from different countries send me information using their preferred formats, as i need the information in date value and in British format. On the part regarding more information, usually when people send me their data, i assume that it is eithier American or British, so if it is "DD\MM\YYYY" or "D\M\YYYY" i will take it as British and if it is "MM\DD\YYYY" or "M\D\YYYY" as American. Would you fellas be able to help me on this? Thank you. Rgds Ray "Gary''s Student" wrote: You need more information. For example 3\7\2009 might mean March 7 2009 in format #6 or July 3 2009 in format #3 -- Gary''s Student - gsnu200907 "swiftcode" wrote: Hi all, I have a problem with some date formats where people send to me in various forms (text format) 1) "DD\MM\YYYY" 2) "D\MM\YYYY" 3) "D\M\YYYY" 4) "MM\DD\YYYY" 5) "MM\D\YYYY" 6) "M\D\YYYY" Is there anyway to be able to convert these to date values and be reflected as "DD\MM\YYY" using vba. Thank you for any help rendered in advance. Rgds Ray |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Date formats in text to be recognised as date value
Hi Guys,
I was thinking of creating a dialog box with 3 options, one for American Date (Text), 1 for British Date (Text) and the late for any date values (Just need to format) to display accordingly. One of the formulas should be something like this: ------------------------------------------------------------------------------------- Function Date_Formating(CurrDate) Dim Date_Val As Date Application.Volatile True Date_Length = Len(Trim(CurrDate)) Select Case Date_Length Case 10 Date_Val = Right(Left(CurrDate, 5), 2) & "/" _ & Left(CurrDate, 2) & "/" & Right(CurrDate, 4) Case 8 Date_Val = Right(Left(CurrDate, 3), 1) & "/" _ & Left(CurrDate, 1) & "/" & Right(CurrDate, 4) Case 9 If Right(Left(CurrDate, 2), 1) = "/" Then Date_Val = Right(Left(CurrDate, 4), 2) & _ "/" & Left(CurrDate, 1) & "/" & Right(CurrDate, 4) ElseIf Right(Left(CurrDate, 3), 1) = "/" Then Date_Val = Right(Left(CurrDate, 4), 1) & _ "/" & Left(CurrDate, 2) & "/" & Right(CurrDate, 4) End If Case Else Date_Val = "ERROR" End Select Date_Formating = Date_Val End Function ------------------------------------------------------------------------------------- Would appreciate any pointers. Thanks Ray "swiftcode" wrote: Hi Rick & Gary, I understand what was said. What i meant was the when people send me their information, usually there are a few hundred lines of data, and 1 column dedicated to the dates, which is generated from some program. As there are dates in the spreadsheets provided, will usually be in either (entirely) British or American format. hence once i identify that, then it is easier to determine whether dates like 3/7/2009 is either stated in (D/M/YYYY or M/D/YYYY), as there will ve other dates for me to eyeball and see the format e.g. 3/21/2009, which effectively tells me that it is "M/DD/YYYY". My apologies in not being able to better describe the senario better. It is not possible to take control and specifically ask for date formats to suit me as these data providers are not obligate to give me the information. Would anyone be able to help? My idea is that if i know its British format, then i will invoke 1 set of vbas, if its American format, another. Thanks Rgds Ray "Gary''s Student" wrote: Try to take control of the material you are given. Ask for dates like: 18 January 2009 Why select an ambiguous format when non-ambiguous formats are available?? -- Gary''s Student - gsnu200907 "swiftcode" wrote: Hi Gary and Joel, Thanks. You are both right. This is a problem for me when people from different countries send me information using their preferred formats, as i need the information in date value and in British format. On the part regarding more information, usually when people send me their data, i assume that it is eithier American or British, so if it is "DD\MM\YYYY" or "D\M\YYYY" i will take it as British and if it is "MM\DD\YYYY" or "M\D\YYYY" as American. Would you fellas be able to help me on this? Thank you. Rgds Ray "Gary''s Student" wrote: You need more information. For example 3\7\2009 might mean March 7 2009 in format #6 or July 3 2009 in format #3 -- Gary''s Student - gsnu200907 "swiftcode" wrote: Hi all, I have a problem with some date formats where people send to me in various forms (text format) 1) "DD\MM\YYYY" 2) "D\MM\YYYY" 3) "D\M\YYYY" 4) "MM\DD\YYYY" 5) "MM\D\YYYY" 6) "M\D\YYYY" Is there anyway to be able to convert these to date values and be reflected as "DD\MM\YYY" using vba. Thank you for any help rendered in advance. Rgds Ray |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Date formats in text to be recognised as date value
On Thu, 15 Oct 2009 09:31:10 -0700, swiftcode
wrote: Hi Rick & Gary, I understand what was said. What i meant was the when people send me their information, usually there are a few hundred lines of data, and 1 column dedicated to the dates, which is generated from some program. As there are dates in the spreadsheets provided, will usually be in either (entirely) British or American format. hence once i identify that, then it is easier to determine whether dates like 3/7/2009 is either stated in (D/M/YYYY or M/D/YYYY), as there will ve other dates for me to eyeball and see the format e.g. 3/21/2009, which effectively tells me that it is "M/DD/YYYY". My apologies in not being able to better describe the senario better. It is not possible to take control and specifically ask for date formats to suit me as these data providers are not obligate to give me the information. Would anyone be able to help? My idea is that if i know its British format, then i will invoke 1 set of vbas, if its American format, another. Thanks Rgds Ray You indicate that these are "spreadsheets". What do you mean by this? If these are Excel workbooks, and the dates have been entered as "dates", then they are stored as serial numbers and you can just convert the format of the cells to your desired "dd/mm/yyyy". If these are text entries, into Excel cells formatted as text, then one approach would be to loop through the range of cells, testing the first and second sections. If section 1 contains an entry 12, then the dates are d/m/y format. If section 2 contains an entry 12, then the dates are m/d/y format If both or neither contain an entry 12, then the dates are indeterminate. So something like: ================================ Option Explicit Sub ConvertDate() Dim rg As Range, c As Range Dim d As Long, m As Long, y As Long Dim dts As Variant Dim A As Boolean, B As Boolean 'set to range where there are dates Set rg = Selection 'loop through range to check max and min entry 'in first and second sections For Each c In rg dts = Split(c.Value, "/") If dts(0) d Then d = dts(0) If dts(1) m Then m = dts(1) Next c If d 12 Then B = True If m 12 Then A = True If A = B Then MsgBox ("Date Format Indeterminate") Exit Sub End If For Each c In rg dts = Split(c.Value, "/") c.NumberFormat = "dd/mm/yyyy" If A = True Then c.Value = DateSerial(dts(2), dts(0), dts(1)) Else c.Value = DateSerial(dts(2), dts(1), dts(0)) End If Next c End Sub ================================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Formats in Text Box | Excel Discussion (Misc queries) | |||
The Cell formats keep changing itself from text to date | Excel Discussion (Misc queries) | |||
US date needs reformatting but not recognised by my UK computer | Excel Programming | |||
how do i change 20050614 within in a cell to a recognised date | Excel Worksheet Functions | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |