|
How to check if cell value is displayed as hashes?
I have a routine where a column with integer values is formatted as dates.
When the value can't be displayed as date, for example something like 18500618, it will be displayed in the sheet as hashes. I need to check for this happening and do a conversion. I have tried to do this check on the actual value in the cell, but that doesn't always work, so I would like to check for the displayed hashes. This now is not that easy. Just checking for the character # isn't foolproof. Thanks for any advice. RBS |
How to check if cell value is displayed as hashes?
Hi Bart,
rather then testing for hashes you'd need to check the number itself. the hashes are just indicating that the cell's formatted output is too wide for the current column width, but could represent a valid number or date, AND could represent errors like #N/A! with following formula you'd check that the value in A2 is a number which, WHEN evaluated/formatted as a date, would represent a date in this century =AND(A1=DATEVALUE("1-1-2000"),A1<DATEVALUE("1-1-2100")) for speed (not clarity) you can change the formula to: =AND(A1=36526,A1<73051) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: I have a routine where a column with integer values is formatted as dates. When the value can't be displayed as date, for example something like 18500618, it will be displayed in the sheet as hashes. I need to check for this happening and do a conversion. I have tried to do this check on the actual value in the cell, but that doesn't always work, so I would like to check for the displayed hashes. This now is not that easy. Just checking for the character # isn't foolproof. Thanks for any advice. RBS |
How to check if cell value is displayed as hashes?
I've seen this is a continuance of another thread.
pls specify what should happen if the interbase date is "pre 1900" and i'll post complete conversion code, rather then the error prone conversion attempted in mentioned thread. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: I have a routine where a column with integer values is formatted as dates. When the value can't be displayed as date, for example something like 18500618, it will be displayed in the sheet as hashes. I need to check for this happening and do a conversion. I have tried to do this check on the actual value in the cell, but that doesn't always work, so I would like to check for the displayed hashes. This now is not that easy. Just checking for the character # isn't foolproof. Thanks for any advice. RBS |
How to check if cell value is displayed as hashes?
On Sun, 3 Oct 2004 12:47:14 +0100, "RB Smissaert"
wrote: I have a routine where a column with integer values is formatted as dates. When the value can't be displayed as date, for example something like 18500618, it will be displayed in the sheet as hashes. I need to check for this happening and do a conversion. I have tried to do this check on the actual value in the cell, but that doesn't always work, so I would like to check for the displayed hashes. This now is not that easy. Just checking for the character # isn't foolproof. Thanks for any advice. RBS It's easier if you keep threads together. But under what circumstance does the test I posted in my previous response to you: ============== If InStr(1, c.Text, "#") = 1 Then =============== not work? --ron |
How to check if cell value is displayed as hashes?
I thought I would start a new thread as I would like to narrow it down to
this particular problem (see if a cell displays the hashes). But under what circumstance does the test I posted in my previous response to etc. I have come across one instance where the cell value was something like 6695, no hashes were displayed, but the instr function found a hash. Have no idea how this happened, but I didn't think the instr function was 100% foolproof. RBS "Ron Rosenfeld" wrote in message ... On Sun, 3 Oct 2004 12:47:14 +0100, "RB Smissaert" wrote: I have a routine where a column with integer values is formatted as dates. When the value can't be displayed as date, for example something like 18500618, it will be displayed in the sheet as hashes. I need to check for this happening and do a conversion. I have tried to do this check on the actual value in the cell, but that doesn't always work, so I would like to check for the displayed hashes. This now is not that easy. Just checking for the character # isn't foolproof. Thanks for any advice. RBS It's easier if you keep threads together. But under what circumstance does the test I posted in my previous response to you: ============== If InStr(1, c.Text, "#") = 1 Then =============== not work? --ron |
How to check if cell value is displayed as hashes?
What should happen when there is a pre 1900 Interbase date (for example
18500615) is that the integer number gets converted to a string representing that date with a specified format. This format is supplied as a string and could be something like "ddd d/m/yyyy". I think I now have something that should always (?) work: Sub ChangeHashesToDates(ByRef rng As Range, Optional ByVal strFormat As String = "") Dim c As Range Application.StatusBar = _ " Converting pre Excel dates, please wait ..." If strFormat = "" Then For Each c In rng If c.Value2 < 19000000 And c.Value2 10000000 Then c.NumberFormat = "0" c.Text = Right(c.Value, 2) & "/" & _ Mid(c.Value, 5, 2) & "/" & _ Left(c.Value, 4) End If Next Else For Each c In rng If c.Value2 < 19000000 And c.Value2 10000000 Then c.NumberFormat = "0" c = Format(DateSerial(Left(c, 4), _ Mid(c, 5, 2), _ Right(c, 2)), _ strFormat) End If Next End If End Sub RBS "keepITcool" wrote in message ... I've seen this is a continuance of another thread. pls specify what should happen if the interbase date is "pre 1900" and i'll post complete conversion code, rather then the error prone conversion attempted in mentioned thread. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: I have a routine where a column with integer values is formatted as dates. When the value can't be displayed as date, for example something like 18500618, it will be displayed in the sheet as hashes. I need to check for this happening and do a conversion. I have tried to do this check on the actual value in the cell, but that doesn't always work, so I would like to check for the displayed hashes. This now is not that easy. Just checking for the character # isn't foolproof. Thanks for any advice. RBS |
How to check if cell value is displayed as hashes?
Bart,
try: Sub ConvertIBDatesToStrings() Dim c As Range, r As Range Set r = Selection For Each c In r.Cells If IsNumeric(c) Then Select Case Val(c) Case 0 To CLng(#1/1/3000#) 'it's a date, pass Case 0 To 29993112 'VBA localizes date. Inserted into Excel as string c.Value = Format( _ DateSerial(Mid(c, 1, 4), Mid(c, 5, 2), Mid(c, 7, 2)), _ "'ddd dd/mm/yyyy") End Select End If Next End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: What should happen when there is a pre 1900 Interbase date (for example 18500615) is that the integer number gets converted to a string representing that date with a specified format. This format is supplied as a string and could be something like "ddd d/m/yyyy". |
How to check if cell value is displayed as hashes?
KeeoITCool,
Thanks, will give that a try. Why you think it is better than the code I gave last? RBS "keepITcool" wrote in message ... Bart, try: Sub ConvertIBDatesToStrings() Dim c As Range, r As Range Set r = Selection For Each c In r.Cells If IsNumeric(c) Then Select Case Val(c) Case 0 To CLng(#1/1/3000#) 'it's a date, pass Case 0 To 29993112 'VBA localizes date. Inserted into Excel as string c.Value = Format( _ DateSerial(Mid(c, 1, 4), Mid(c, 5, 2), Mid(c, 7, 2)), _ "'ddd dd/mm/yyyy") End Select End If Next End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: What should happen when there is a pre 1900 Interbase date (for example 18500615) is that the integer number gets converted to a string representing that date with a specified format. This format is supplied as a string and could be something like "ddd d/m/yyyy". |
How to check if cell value is displayed as hashes?
On Sun, 3 Oct 2004 13:39:59 +0100, "RB Smissaert"
wrote: I have come across one instance where the cell value was something like 6695, no hashes were displayed, but the instr function found a hash. Have no idea how this happened, but I didn't think the instr function was 100% foolproof. RBS The only way I could get instr to find a hash was if one was displayed in the cell. If the cell is date formatted, it can also display a hash if the column is too narrow. But you wrote that the cell did NOT display a hash, yet instr found one. I find that very curious. Perhaps you can provide enough information to run this down further, as just putting 6695 in a cell, formatting it as a date, and running the instr test does not result in instr finding a hash. If, in fact, the cell is displaying a hash because the cell is too narrow, just do an autofit before running the instr test. For example: ================= Sub foo() Dim c As Range Selection.Columns.AutoFit For Each c In Selection If InStr(1, c.Text, "#") = 1 Then MsgBox (c.Text) End If Next c End Sub ===================== --ron |
How to check if cell value is displayed as hashes?
Tried your code, but it didn't work.
It fell over on IsNumeric(c) After some adjustments I got it to work: Sub ConvertIBDatesToStrings(ByRef rng As Range, Optional ByVal strFormat As String = "") Application.StatusBar = _ " Converting pre Excel dates, please wait ..." Dim c As Range If strFormat = "" Then For Each c In rng.Cells Select Case c.Value2 Case 0 To CLng(#1/1/3000#) 'it's a date, pass Case 0 To 29993112 'VBA localizes date. Inserted into Excel as string c = Mid(c, 7, 2) & "/" & _ Mid(c, 5, 2) & "/" & _ Left(c, 4) End Select Next Else For Each c In rng.Cells Select Case c.Value2 Case 0 To CLng(#1/1/3000#) 'it's a date, pass Case 0 To 29993112 c.NumberFormat = "0" 'VBA localizes date. Inserted into Excel as string c = Format(DateSerial(Left(c, 4), _ Mid(c, 5, 2), _ Right(c, 2)), _ strFormat) End Select Next End If End Sub RBS "keepITcool" wrote in message ... Bart, try: Sub ConvertIBDatesToStrings() Dim c As Range, r As Range Set r = Selection For Each c In r.Cells If IsNumeric(c) Then Select Case Val(c) Case 0 To CLng(#1/1/3000#) 'it's a date, pass Case 0 To 29993112 'VBA localizes date. Inserted into Excel as string c.Value = Format( _ DateSerial(Mid(c, 1, 4), Mid(c, 5, 2), Mid(c, 7, 2)), _ "'ddd dd/mm/yyyy") End Select End If Next End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: What should happen when there is a pre 1900 Interbase date (for example 18500615) is that the integer number gets converted to a string representing that date with a specified format. This format is supplied as a string and could be something like "ddd d/m/yyyy". |
How to check if cell value is displayed as hashes?
Full agree with you.
Perhaps it was just the column being too narrow. Haven't been able to reproduce this. RBS "Ron Rosenfeld" wrote in message ... On Sun, 3 Oct 2004 13:39:59 +0100, "RB Smissaert" wrote: I have come across one instance where the cell value was something like 6695, no hashes were displayed, but the instr function found a hash. Have no idea how this happened, but I didn't think the instr function was 100% foolproof. RBS The only way I could get instr to find a hash was if one was displayed in the cell. If the cell is date formatted, it can also display a hash if the column is too narrow. But you wrote that the cell did NOT display a hash, yet instr found one. I find that very curious. Perhaps you can provide enough information to run this down further, as just putting 6695 in a cell, formatting it as a date, and running the instr test does not result in instr finding a hash. If, in fact, the cell is displaying a hash because the cell is too narrow, just do an autofit before running the instr test. For example: ================= Sub foo() Dim c As Range Selection.Columns.AutoFit For Each c In Selection If InStr(1, c.Text, "#") = 1 Then MsgBox (c.Text) End If Next c End Sub ===================== --ron |
How to check if cell value is displayed as hashes?
KeepITCool,
Worked this a bit further out by looking at the possible extremes of the Interbase dates. This is what I have now and I think this should work in all situations. I also tweaked it a bit for speed. Seems an If construction is slightly faster than a Select Case construction. Also I don't think Mid is faster than Left or Right in this particular Sub. There is no need for IsNumeric as the data can't be anything else than integer numerics. Sub ConvertIBDatesToStrings(ByRef rng As Range, Optional ByVal strFormat As String = "") 'note that the highest possible Excel integer date is 2958465 or friday 31 dec 9999 '---------------------------------------------------------------------------------- Dim c As Range Application.StatusBar = _ " Converting pre Excel dates, please wait ..." If strFormat = "" Then For Each c In rng.Cells If c.Value2 = 0 Then 'otherwise this will appear in the sheet as 00/01/1900 '----------------------------------------------------- c.NumberFormat = "0" c = 0 Else If c.Value2 18000100 And _ c.Value2 < 18993113 Then 'the earliest date possible in Synergy is 01/01/1800 'above 31/12/1899 will be handled by Excel 'don't know about S6000 'Interbase date, insert into Excel as string '--------------------------------------------------- c.NumberFormat = "0" c = Right(c, 2) & "/" & _ Mid(c, 5, 2) & "/" & _ Left(c, 4) End If End If Next Else For Each c In rng.Cells If c.Value2 = 0 Then c.NumberFormat = "0" c = 0 Else If c.Value2 18000100 And _ c.Value2 < 18993113 Then c.NumberFormat = "0" c = Format(DateSerial(Left(c, 4), _ Mid(c, 5, 2), _ Right(c, 2)), _ strFormat) End If End If Next End If End Sub RBS "keepITcool" wrote in message ... Bart, try: Sub ConvertIBDatesToStrings() Dim c As Range, r As Range Set r = Selection For Each c In r.Cells If IsNumeric(c) Then Select Case Val(c) Case 0 To CLng(#1/1/3000#) 'it's a date, pass Case 0 To 29993112 'VBA localizes date. Inserted into Excel as string c.Value = Format( _ DateSerial(Mid(c, 1, 4), Mid(c, 5, 2), Mid(c, 7, 2)), _ "'ddd dd/mm/yyyy") End Select End If Next End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: What should happen when there is a pre 1900 Interbase date (for example 18500615) is that the integer number gets converted to a string representing that date with a specified format. This format is supplied as a string and could be something like "ddd d/m/yyyy". |
How to check if cell value is displayed as hashes?
Bart, I see 1 big danger in your code.
the first loop are pure strings. UNFORMATTED by VBA/EXCEL. your POST 1900 dates are dateserials converted to strings. if strFormat = "dd/mm/yyyy" that line would produce dates like "15-03- 2004" on my machine. not as YOU anticipate "15/03/2004" ALSO NOTE: you set the cell format to "0", but you intend to insert a string. it would be better: to set the numberformat to text ( "@" ) OR append an apostrophe to the beginning of the inserted string. i dont know how many cells you need to process and if the range only contains these dates.. you could consider using an intermediate array assuming the array is 'vertical' dim v,i& v=r.value2 for i = 1 to ubound(v,1) v(i,1) = ...convert next r.numberformat="0" r.value2 = v less overhead because you write to the range only once, you format the entire range iso cells keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: KeepITCool, Worked this a bit further out by looking at the possible extremes of the Interbase dates. This is what I have now and I think this should work in all situations. I also tweaked it a bit for speed. Seems an If construction is slightly faster than a Select Case construction. Also I don't think Mid is faster than Left or Right in this particular Sub. There is no need for IsNumeric as the data can't be anything else than integer numerics. Sub ConvertIBDatesToStrings(ByRef rng As Range, Optional ByVal strFormat As String = "") 'note that the highest possible Excel integer date is 2958465 or friday 31 dec 9999 '------------------------------------------------------------------ ---------------- Dim c As Range Application.StatusBar = _ " Converting pre Excel dates, please wait ..." If strFormat = "" Then For Each c In rng.Cells If c.Value2 = 0 Then 'otherwise this will appear in the sheet as 00/01/1900 '----------------------------------------------------- c.NumberFormat = "0" c = 0 Else If c.Value2 18000100 And _ c.Value2 < 18993113 Then 'the earliest date possible in Synergy is 01/01/1800 'above 31/12/1899 will be handled by Excel 'don't know about S6000 'Interbase date, insert into Excel as string '-------------------------------------------------- - c.NumberFormat = "0" c = Right(c, 2) & "/" & _ Mid(c, 5, 2) & "/" & _ Left(c, 4) End If End If Next Else For Each c In rng.Cells If c.Value2 = 0 Then c.NumberFormat = "0" c = 0 Else If c.Value2 18000100 And _ c.Value2 < 18993113 Then c.NumberFormat = "0" c = Format(DateSerial(Left(c, 4), _ Mid(c, 5, 2), _ Right(c, 2)), _ strFormat) End If End If Next End If End Sub RBS "keepITcool" wrote in message ... Bart, try: Sub ConvertIBDatesToStrings() Dim c As Range, r As Range Set r = Selection For Each c In r.Cells If IsNumeric(c) Then Select Case Val(c) Case 0 To CLng(#1/1/3000#) 'it's a date, pass Case 0 To 29993112 'VBA localizes date. Inserted into Excel as string c.Value = Format( _ DateSerial(Mid(c, 1, 4), Mid(c, 5, 2), Mid(c, 7, 2)), _ "'ddd dd/mm/yyyy") End Select End If Next End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: What should happen when there is a pre 1900 Interbase date (for example 18500615) is that the integer number gets converted to a string representing that date with a specified format. This format is supplied as a string and could be something like "ddd d/m/yyyy". |
How to check if cell value is displayed as hashes?
Hallo KeepITCool,
Sorry, I don't quite get you. Firstly, the code as posted will leave the normal post 1900 dates alone as the values (the Excel integer date) will be below 18000101. Secondly, not sure why the format "dd/mm/yyyy" would give dd-mm-yyyy. I tried and it gave dd/mm/yyyy with me as expected. I agree making the number format "0" first and then putting a string in is a bit strange, but I can see no harm in it and it saves an extra manipulation. I need to make a number format first otherwise I get the hashes. Working with an intermediate array would be a bit awkward as I would have to keep track of the location of the cells with the IB date as these are intermixed with the cells with the normal post 1900 date. I could do a sort in the sheet first on the date and then sort back to ID numbers in column A, but that would take time as well. The row numbers we are dealing with are up to about 30000 for the total number and up to a few 100 for the pre 1900 dates. Hope this makes it all a bit clearer. Groeten van Bart "keepITcool" wrote in message ... Bart, I see 1 big danger in your code. the first loop are pure strings. UNFORMATTED by VBA/EXCEL. your POST 1900 dates are dateserials converted to strings. if strFormat = "dd/mm/yyyy" that line would produce dates like "15-03- 2004" on my machine. not as YOU anticipate "15/03/2004" ALSO NOTE: you set the cell format to "0", but you intend to insert a string. it would be better: to set the numberformat to text ( "@" ) OR append an apostrophe to the beginning of the inserted string. i dont know how many cells you need to process and if the range only contains these dates.. you could consider using an intermediate array assuming the array is 'vertical' dim v,i& v=r.value2 for i = 1 to ubound(v,1) v(i,1) = ...convert next r.numberformat="0" r.value2 = v less overhead because you write to the range only once, you format the entire range iso cells keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: KeepITCool, Worked this a bit further out by looking at the possible extremes of the Interbase dates. This is what I have now and I think this should work in all situations. I also tweaked it a bit for speed. Seems an If construction is slightly faster than a Select Case construction. Also I don't think Mid is faster than Left or Right in this particular Sub. There is no need for IsNumeric as the data can't be anything else than integer numerics. Sub ConvertIBDatesToStrings(ByRef rng As Range, Optional ByVal strFormat As String = "") 'note that the highest possible Excel integer date is 2958465 or friday 31 dec 9999 '------------------------------------------------------------------ ---------------- Dim c As Range Application.StatusBar = _ " Converting pre Excel dates, please wait ..." If strFormat = "" Then For Each c In rng.Cells If c.Value2 = 0 Then 'otherwise this will appear in the sheet as 00/01/1900 '----------------------------------------------------- c.NumberFormat = "0" c = 0 Else If c.Value2 18000100 And _ c.Value2 < 18993113 Then 'the earliest date possible in Synergy is 01/01/1800 'above 31/12/1899 will be handled by Excel 'don't know about S6000 'Interbase date, insert into Excel as string '-------------------------------------------------- - c.NumberFormat = "0" c = Right(c, 2) & "/" & _ Mid(c, 5, 2) & "/" & _ Left(c, 4) End If End If Next Else For Each c In rng.Cells If c.Value2 = 0 Then c.NumberFormat = "0" c = 0 Else If c.Value2 18000100 And _ c.Value2 < 18993113 Then c.NumberFormat = "0" c = Format(DateSerial(Left(c, 4), _ Mid(c, 5, 2), _ Right(c, 2)), _ strFormat) End If End If Next End If End Sub RBS "keepITcool" wrote in message ... Bart, try: Sub ConvertIBDatesToStrings() Dim c As Range, r As Range Set r = Selection For Each c In r.Cells If IsNumeric(c) Then Select Case Val(c) Case 0 To CLng(#1/1/3000#) 'it's a date, pass Case 0 To 29993112 'VBA localizes date. Inserted into Excel as string c.Value = Format( _ DateSerial(Mid(c, 1, 4), Mid(c, 5, 2), Mid(c, 7, 2)), _ "'ddd dd/mm/yyyy") End Select End If Next End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: What should happen when there is a pre 1900 Interbase date (for example 18500615) is that the integer number gets converted to a string representing that date with a specified format. This format is supplied as a string and could be something like "ddd d/m/yyyy". |
How to check if cell value is displayed as hashes?
KeepITCool,
Your suggestion to make the cells with the pre 1900 date a text format was right. It will still change the hashes to a number, so I have changed that now. So now I have: Sub ConvertIBDatesToStrings(ByRef rng As Range, Optional ByVal strFormat As String = "") 'note that the highest possible Excel integer date is 2958465 or friday 31 dec 9999 '---------------------------------------------------------------------------------- Dim c As Range Application.StatusBar = _ " Converting pre Excel dates, please wait ..." If strFormat = "" Then For Each c In rng.Cells If c.Value2 = 0 Then 'otherwise this will appear in the sheet as 00/01/1900 '----------------------------------------------------- c.NumberFormat = "0" c = 0 Else If c.Value2 18000100 And _ c.Value2 < 18993113 Then 'the earliest date possible in Synergy is 01/01/1800 'above 31/12/1899 will be handled by Excel 'don't know about S6000 'Interbase date, insert into Excel as string '--------------------------------------------------- c.NumberFormat = "@" c = Right(c, 2) & "/" & _ Mid(c, 5, 2) & "/" & _ Left(c, 4) End If End If Next Else For Each c In rng.Cells If c.Value2 = 0 Then c.NumberFormat = "0" c = 0 Else If c.Value2 18000100 And _ c.Value2 < 18993113 Then c.NumberFormat = "@" c = Format(DateSerial(Left(c, 4), _ Mid(c, 5, 2), _ Right(c, 2)), _ strFormat) End If End If Next End If End Sub Bart "keepITcool" wrote in message ... Bart, I see 1 big danger in your code. the first loop are pure strings. UNFORMATTED by VBA/EXCEL. your POST 1900 dates are dateserials converted to strings. if strFormat = "dd/mm/yyyy" that line would produce dates like "15-03- 2004" on my machine. not as YOU anticipate "15/03/2004" ALSO NOTE: you set the cell format to "0", but you intend to insert a string. it would be better: to set the numberformat to text ( "@" ) OR append an apostrophe to the beginning of the inserted string. i dont know how many cells you need to process and if the range only contains these dates.. you could consider using an intermediate array assuming the array is 'vertical' dim v,i& v=r.value2 for i = 1 to ubound(v,1) v(i,1) = ...convert next r.numberformat="0" r.value2 = v less overhead because you write to the range only once, you format the entire range iso cells keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: KeepITCool, Worked this a bit further out by looking at the possible extremes of the Interbase dates. This is what I have now and I think this should work in all situations. I also tweaked it a bit for speed. Seems an If construction is slightly faster than a Select Case construction. Also I don't think Mid is faster than Left or Right in this particular Sub. There is no need for IsNumeric as the data can't be anything else than integer numerics. Sub ConvertIBDatesToStrings(ByRef rng As Range, Optional ByVal strFormat As String = "") 'note that the highest possible Excel integer date is 2958465 or friday 31 dec 9999 '------------------------------------------------------------------ ---------------- Dim c As Range Application.StatusBar = _ " Converting pre Excel dates, please wait ..." If strFormat = "" Then For Each c In rng.Cells If c.Value2 = 0 Then 'otherwise this will appear in the sheet as 00/01/1900 '----------------------------------------------------- c.NumberFormat = "0" c = 0 Else If c.Value2 18000100 And _ c.Value2 < 18993113 Then 'the earliest date possible in Synergy is 01/01/1800 'above 31/12/1899 will be handled by Excel 'don't know about S6000 'Interbase date, insert into Excel as string '-------------------------------------------------- - c.NumberFormat = "0" c = Right(c, 2) & "/" & _ Mid(c, 5, 2) & "/" & _ Left(c, 4) End If End If Next Else For Each c In rng.Cells If c.Value2 = 0 Then c.NumberFormat = "0" c = 0 Else If c.Value2 18000100 And _ c.Value2 < 18993113 Then c.NumberFormat = "0" c = Format(DateSerial(Left(c, 4), _ Mid(c, 5, 2), _ Right(c, 2)), _ strFormat) End If End If Next End If End Sub RBS "keepITcool" wrote in message ... Bart, try: Sub ConvertIBDatesToStrings() Dim c As Range, r As Range Set r = Selection For Each c In r.Cells If IsNumeric(c) Then Select Case Val(c) Case 0 To CLng(#1/1/3000#) 'it's a date, pass Case 0 To 29993112 'VBA localizes date. Inserted into Excel as string c.Value = Format( _ DateSerial(Mid(c, 1, 4), Mid(c, 5, 2), Mid(c, 7, 2)), _ "'ddd dd/mm/yyyy") End Select End If Next End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "RB Smissaert" wrote: What should happen when there is a pre 1900 Interbase date (for example 18500615) is that the integer number gets converted to a string representing that date with a specified format. This format is supplied as a string and could be something like "ddd d/m/yyyy". |
| All times are GMT +1. The time now is 06:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com