| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date/Time - String or Number!
I am importing some data from a database via a VBA String.
One of the fields is a Date/Time in the form of dd/yy/mm hh:dd:ss. I stick the string into a cell using Set xLrngCell = Worksheets("Sheet1").Cells.Item(lngRowCount, lngColCount) xLrngCell = strFieldContents However when I go to the spreadsheet I find that some of the dates will not convert to their numeric value. If I try to format them as a General number some of them convert but others stay as strings. If I edit one of these strings (just removing and replacing one character) it will "become" OK. What is going on? Thanks, Roy |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date/Time - String or Number!
You say that the date is in dd/mm format. Is this the format that
your date is set? It is possible that it is treating the dates as mm/dd and that some are therefore not possible dates. Example 01/01 is 1 Jan in dd/mm and mm/dd 15/01 is 15 Jan in dd/mm but not a date in mm/dd so will be treated as text. Chrissy. RzB wrote I am importing some data from a database via a VBA String. One of the fields is a Date/Time in the form of dd/yy/mm hh:dd:ss. I stick the string into a cell using Set xLrngCell = Worksheets("Sheet1").Cells.Item(lngRowCount, lngColCount) xLrngCell = strFieldContents However when I go to the spreadsheet I find that some of the dates will not convert to their numeric value. If I try to format them as a General number some of them convert but others stay as strings. If I edit one of these strings (just removing and replacing one character) it will "become" OK. What is going on? Thanks, Roy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date/Time - String or Number!
Ooopppssss - error error - Apols..
Date format is dd/mm/yyyy hh:mm:ss.... Doh.... Must have had a brain failure.... Getting too old for all this high powered programming.... :-) Dates are all reasonable... 24/06/2002 0:33:18 24/06/2002 0:34:41 17/07/2002 8:51:06 08/03/2002 6:42:28 08/04/2002 0:22:08 08/04/2002 1:15:01 You are probably on the right track though... The top three do not convert but the bottom three do.... However - if I select the top cell, edit the 8 on the end and replace it with an 8 it works just fine.... Thanks for your help... Roy "Chrissy" wrote in message ... You say that the date is in dd/mm format. Is this the format that your date is set? It is possible that it is treating the dates as mm/dd and that some are therefore not possible dates. Example 01/01 is 1 Jan in dd/mm and mm/dd 15/01 is 15 Jan in dd/mm but not a date in mm/dd so will be treated as text. Chrissy. RzB wrote I am importing some data from a database via a VBA String. One of the fields is a Date/Time in the form of dd/yy/mm hh:dd:ss. I stick the string into a cell using Set xLrngCell = Worksheets("Sheet1").Cells.Item(lngRowCount, lngColCount) xLrngCell = strFieldContents However when I go to the spreadsheet I find that some of the dates will not convert to their numeric value. If I try to format them as a General number some of them convert but others stay as strings. If I edit one of these strings (just removing and replacing one character) it will "become" OK. What is going on? Thanks, Roy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date/Time - String or Number!
Having real finger trouble today....
Dates are... 24/06/2002 10:33:18 24/06/2002 10:34:41 17/07/2002 18:51:06 08/03/2002 16:42:28 08/04/2002 10:22:08 08/04/2002 11:15:01 Apols... Roy "RzB" wrote in message ... Ooopppssss - error error - Apols.. Date format is dd/mm/yyyy hh:mm:ss.... Doh.... Must have had a brain failure.... Getting too old for all this high powered programming.... :-) Dates are all reasonable... 24/06/2002 0:33:18 24/06/2002 0:34:41 17/07/2002 8:51:06 08/03/2002 6:42:28 08/04/2002 0:22:08 08/04/2002 1:15:01 You are probably on the right track though... The top three do not convert but the bottom three do.... However - if I select the top cell, edit the 8 on the end and replace it with an 8 it works just fine.... Thanks for your help... Roy "Chrissy" wrote in message ... You say that the date is in dd/mm format. Is this the format that your date is set? It is possible that it is treating the dates as mm/dd and that some are therefore not possible dates. Example 01/01 is 1 Jan in dd/mm and mm/dd 15/01 is 15 Jan in dd/mm but not a date in mm/dd so will be treated as text. Chrissy. RzB wrote I am importing some data from a database via a VBA String. One of the fields is a Date/Time in the form of dd/yy/mm hh:dd:ss. I stick the string into a cell using Set xLrngCell = Worksheets("Sheet1").Cells.Item(lngRowCount, lngColCount) xLrngCell = strFieldContents However when I go to the spreadsheet I find that some of the dates will not convert to their numeric value. If I try to format them as a General number some of them convert but others stay as strings. If I edit one of these strings (just removing and replacing one character) it will "become" OK. What is going on? Thanks, Roy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date/Time - String or Number!
It would appear that excel is not converting the dates that look
like they may be in error if they were mm/dd rather than dd/mm. Why should this be? Settings are obviously correct as shown by simple editing of cell with same data. Am I missing something? Roy "RzB" wrote in message ... Having real finger trouble today.... Dates are... 24/06/2002 10:33:18 24/06/2002 10:34:41 17/07/2002 18:51:06 08/03/2002 16:42:28 08/04/2002 10:22:08 08/04/2002 11:15:01 Apols... Roy "RzB" wrote in message ... Ooopppssss - error error - Apols.. Date format is dd/mm/yyyy hh:mm:ss.... Doh.... Must have had a brain failure.... Getting too old for all this high powered programming.... :-) Dates are all reasonable... 24/06/2002 0:33:18 24/06/2002 0:34:41 17/07/2002 8:51:06 08/03/2002 6:42:28 08/04/2002 0:22:08 08/04/2002 1:15:01 You are probably on the right track though... The top three do not convert but the bottom three do.... However - if I select the top cell, edit the 8 on the end and replace it with an 8 it works just fine.... Thanks for your help... Roy "Chrissy" wrote in message ... You say that the date is in dd/mm format. Is this the format that your date is set? It is possible that it is treating the dates as mm/dd and that some are therefore not possible dates. Example 01/01 is 1 Jan in dd/mm and mm/dd 15/01 is 15 Jan in dd/mm but not a date in mm/dd so will be treated as text. Chrissy. RzB wrote I am importing some data from a database via a VBA String. One of the fields is a Date/Time in the form of dd/yy/mm hh:dd:ss. I stick the string into a cell using Set xLrngCell = Worksheets("Sheet1").Cells.Item(lngRowCount, lngColCount) xLrngCell = strFieldContents However when I go to the spreadsheet I find that some of the dates will not convert to their numeric value. If I try to format them as a General number some of them convert but others stay as strings. If I edit one of these strings (just removing and replacing one character) it will "become" OK. What is going on? Thanks, Roy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting number of rows between date range and meeting 2 string criteria | Excel Worksheet Functions | |||
| Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
| convert string to date & time | Excel Worksheet Functions | |||
| Every time i put a number with period it becomes a date and time | Excel Discussion (Misc queries) | |||
| Counting number of time a character appears in a string | Excel Worksheet Functions | |||
Hybrid Mode
