Date values showing up as ######
Clay
pulled in from another system. The system stores the
dates in the following format: yyyymmdd - with no
spaces or slashes. I would like to display the dates in
my worksheet as mm/dd/yyyy, however when I try to adjust
the cell properties, to a date format, the dates change
to ###################. Does anyone have a suggestion as
to how I can do this?
Thank you in advance,
Clay
J.E. McGimpsey
from the Date dropdown and click Finish.
In article <074d01c2d43d$d89a9c60$a601...@phx.gbl>, Clay
Doug
grabbing the right edge of the cell and making it wider.
I'm going to bet this works for you, like it always has
form me. If not, well maybe someone else will have
another suggestion. Good luck Doug
>
Ken Wright
double-click it will automatically adjust to the necessary size to contain
the widest piece of data in the column.
--
Regards
Ken....................... Win XP / XL2K & XLXP
----------------------------------------------------------------------------
Public Service Request - It is very much appreciated
in text-only groups if you don't attach files - Thanks
----------------------------------------------------------------------------
"Doug" <dra...@att.net> wrote in message
news:008501c2d449$9eaaccd0$a201...@phx.gbl...
J.E. McGimpsey
20030214 is not a valid date.
Using Data/Text to Columns is probably the easiest way to convert.
In article <ejzqu1F1CHA.1780@TK2MSFTNGP11>, Ken Wright
Ken Wright
use regarding his comment on his usual method of dragging column widths, and
not the OP, but I should have clarified it.
--
Regards
Ken....................... Win XP / XL2K & XLXP
----------------------------------------------------------------------------
Public Service Request - It is very much appreciated
in text-only groups if you don't attach files - Thanks
----------------------------------------------------------------------------
"J.E. McGimpsey" <jemcg...@mvps.org> wrote in message
news:140220031239158939%jemcg...@mvps.org...
Norman Harker
Another approach is to parse the date entries.
=DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2))
Format in desired manner for the date.
The resulting return can then be converted to a values if required and you
can copy and paste those values over the existing data.
Having got the number to a date form there is nothing stopping you from
using a custom format that looks like the original entry; just use yyyymmdd;
but note that this only 'looks like' the existing entry. It is in fact an
entirely different number.
It's likely to be quite a common formula requirement because the form of
entry is the ISO8601:2000 approved non-separated date form.
It's a pity that Excel's date entry and date format options don't include
this as practically every country in the World's standards regulating bodies
are members of ISO. The chances of adoption of standards in this area are
being handicapped by Excel being behind the times; the earlier iterations of
the Standard which have specified this format date back to at least 1988.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"J.E. McGimpsey" <jemcg...@mvps.org> wrote in message
news:140220030847404935%jemcg...@mvps.org...
Norman Harker
And since this is the ISO8601:2000 date form, I've written a pretty basic
subroutine that will do the conversion in-situ on a range of selected cells.
This can be attached to a button but for a once only requirement it would be
better in the Personal.xls file or as an addin.
Sub ISONumberToExcelDate()
'Converts Number in ISO8601:2000 format yyyymmdd to
'A date in Excel Date Serial Number range.
'Norman Harker 20030215. No copyright reserved.
Dim Cell As Range: Dim ISOText As Long
Dim ISOYear As Integer: Dim ISOMonth As Integer
Dim ISODay As Integer
'Checks and ends if some cells have dates already.
'Also prevents problems of running twice
For Each Cell In Selection
If IsDate(Cell.Value) Then
MsgBox "Some cells contain dates already"
End
End If
Next
'Text for ISO form of date and Excel's Date Range
For Each Cell In Selection
ISOText = Cell.Value
'Checks and ends if any cell has less than 8 digits
If ISOText < 10000000 Then
MsgBox ("All ISO8601:2000 dates have eight digits")
End
End If
'Checks and ends if any date before 0-Jan-1900
If ISOText < 19000100 Then
MsgBox ("Excel does not accept dates before 1900")
End If
'Checks and ends if any date after 31-Dec-9999
If ISOText > 99991231 Then
MsgBox ("Excel requires dates before 31-Dec-9999")
End
End If
Next
For Each Cell In Selection
ISOText = Cell.Value
ISOYear = Left(ISOText, 4)
ISOMonth = Mid(ISOText, 5, 2)
ISODay = Right(ISOText, 2)
Cell.Value = DateSerial(ISOYear, ISOMonth, ISODay)
'Change format selected to required form
Cell.NumberFormat = "mm/dd/yyyy"
Next
End Sub
There's a fair amount that can be done to improve this but it's a quick and
dirty solution that will cover most problems and will certainly handle the
case of a major block of cells imported from a source that has used the
numeric ISO8601:2000 compliant date entry form. For that you really only
need the Dims up the top and the last For - Next loop.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Norman Harker" <njha...@optusnet.com.au> wrote in message
news:uuzF3WI1CHA.2564@TK2MSFTNGP12...
Norman Harker
JE McGimpsey has done a great job on my code and with a few extra twiddley
bits by me we now have a far better and more robust subroutine that will
perform the important task of converting non-separated forms of ISO8601
dates to dates recognised as dates by Excel.
In case anyone thinks that this is an esoteric exercise, the EEC has begun
to implement the standard under the name E 8601. Also I find on an internet
search that it is being mandated in the United States in some instances such
as for Electronic Health Records. Over time, I suspect we will get quite a
few queries on how to get Excel to understand yyyymmdd numbers.
The format of return is currently set at mm/dd/yyyy. That can be changed
very easily and I would personally prefer yyyy-mm-dd as the format as that
is the ISO8601:2000 approved version for separated date forms and is
recognised by all versions of Excel as a date regardless of System settings,
double digit interpretation settings and language. But I think that most
users will use mm/dd/yyyy and it can be changed by simple re-format of the
cells after the subroutine has been run.
Error message techniques used by JE secure that only one message per fault
type comes up in your range of ISO date numbers. The code will handle 1904
date system settings. If an empty cell gets selected it is skipped over and
the same applies to cells which already have Excel 'understood' dates. If
non accepted numbers are encountered those are made bold and should stand
out better in a large selected range.
Code line length has been economised upon so as to facilitate copying and
pasting from this post.
The code after JE finished with it and I played around more is:
Public Sub ISOToExcelDate()
'Converts Number in ISO8601:2000 format yyyymmdd to
'A date in Excel Date Serial Number range.
'JE McGimpsey and Norman Harker 20030215.
'No copyright reserved.
Const ERR2000STR As String = "Some cells contain dates " & _
"already. These are now highlighted in bold."
Const ERR2001STR As String = "All ISO8601:2000 dates have " & _
"eight digits. Non compliant numbers are now highlighted " & _
"in bold"
Const ERR2002STR As String = "With your current date " & _
"settings Excel does not accept some of your dates " & _
"We have highlighted in bold dates before 190"
Const ERR2003STR As String = "Excel requires dates before " & _
"31-Dec-9999. Dates after that are highlighted in bold"
'Change format string if another format is required. For
' example you may prefer to use yyyy-mm-dd which is ISO
' approved separated form. Some date formats might not be
' recognised by all regional settings.
Const NUMFORMATSTR As String = "mm/dd/yyyy"
Dim Cell As Range
Dim errs(0 To 3) As Boolean
On Error GoTo ErrorHandler
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
With Cell
If IsEmpty(.Value) Then
'Do nothing
ElseIf IsDate(.Value) Then
Err.Raise 2000, Description:=ERR2000STR
ElseIf .Value < 10000000 Then
Cell.Font.FontStyle = "Bold"
Err.Raise 2001, Description:=ERR2001STR
ElseIf .Value < 19000100 - ActiveWorkbook.Date1904 * 1462 Then
Cell.Font.FontStyle = "Bold"
Err.Raise 2002, Description:=CStr(ERR2002STR & _
(-4 * ActiveWorkbook.Date1904))
ElseIf .Value > 99991231 Then
Cell.Font.FontStyle = "Bold"
Err.Raise 2003, Description:=ERR2003STR
Else
.Value = DateSerial(Left(.Text, 4), Mid(.Text, 5, 2), _
Right(.Text, 2))
.NumberFormat = NUMFORMATSTR
End If
End With
Next
On Error GoTo 0
Exit Sub
ErrorHandler:
With Err
If Not errs(.Number - 2000) Then
MsgBox "Error " & .Number & ", " & .Description
errs(.Number - 2000) = True
End If
.Clear
End With
Resume Next
End Sub
Any further improvements gratefully accepted.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Norman Harker" <njha...@optusnet.com.au> wrote in message news:...
Myrna Larson
There are some problems with the data validation.
1. If a cell contains text, such as "abcde", the message implies it's a number > 99991231.
The VBA IsDate function doesn't check the data type of the cell's value -- it checks whether the
contents (usually text) can be *intepreted* as a valid date in the VBA date range. With this
example, that test fails, and you go on to do tests that assume the value is a number, which is
isn't. You finally get a false when testing whether "abcde" is <= 99991231. (I assume that
"under the hood", VB is converting 99991231 to the text "99991231".)
2. With the 1904 date system, a cell containing the *text* "1/1/1902" passes muster as a valid
date (for reasons noted above) and produces the message that the cell already contains a date.
It doesn't, of course, and it can't be converted to a 1904 date, either.
3. If the workbook uses the 1904 system, you check for a value < 19001562 (which isn't a valid
ISO date, either). You should be checking for values < 19040101 (remember, the data hasn't been
converted to a date yet).
I added some optimizations for speed:
1. get the 1900/1904 date setting once at the top and set the minimum date appropriately
2. get the value from the cell once, put it into a VBA variable, and test that variable
3. set the font to bold down in the error handler instead of repeating that line 4 times
4. use integer arithmetic to parse the year, month, and day values instead of string functions
(integer arithmetic is significantly faster).
Public Sub ISOToExcelDate2()
Const NUMFORMATSTR As String = "mm/dd/yyyy"
Dim Cell As Range
Dim Errs(0 To 4) As Boolean
Dim MaxValue As Long
Dim MinValue As Long
Dim n As Long
Dim v As Variant
Dim ErrDescs(0 To 4) As String
ErrDescs(0) = "Some cells contain dates already. " & _
"These are now highlighted in bold."
ErrDescs(1) = "Some cells contain non-numeric data. " & _
"They are highlighted in bold."
ErrDescs(2) = "All ISO8601:2000 dates have eight digits. " & _
"Non compliant numbers are now highlighted in bold."
ErrDescs(3) = "With your current date settings Excel does not accept " & _
"some of your dates. Dates before 1900 are highlighted in bold."
ErrDescs(4) = "Excel requires dates before " & _
"31-Dec-9999. Dates after that are highlighted in bold."
MinValue = 19000101
MaxValue = 99991231
If ActiveWorkbook.Date1904 = True Then
MinValue = 19040101
'next line works in XL2000 and above
ErrDescs(3) = Replace(ErrDescs(3), "1900", "1904")
'for Excel 97, use
'ErrDescs(3) = Application.Substitute(ErrDescs(3), "1900", "1904")
End If
On Error GoTo ErrorHandler
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
With Cell
v = .Value
If IsEmpty(v) Then
'ignore blank cells
ElseIf IsDate(v) Then
Err.Raise 2000
ElseIf IsNumeric(v) = False Then
Err.Raise 2001
'or ignore text ???
ElseIf v < 10000000 Then
Err.Raise 2002
ElseIf v < MinValue Then
Err.Raise 2003
ElseIf v > MaxValue Then
Err.Raise 2004
Else
n = Fix(v)
.Value = DateSerial(n \ 10000, (n \ 100) Mod 100, n Mod 100)
.NumberFormat = NUMFORMATSTR
End If
End With
Next Cell
On Error GoTo 0
Exit Sub
ErrorHandler:
Cell.Font.FontStyle = "Bold"
n = Err.Number
If n >= 2000 Then
n = n - 2000
If Not Errs(n) Then
MsgBox "Error " & (n + 2000) & ", " & ErrDescs(n)
Errs(n) = True
End If
Else
'give message for any other errors, too
MsgBox "Unexpected error " & n & ", " & Err.Description & " occurred."
End If
Err.Clear
Resume Next
End Sub
J.E. McGimpsey
(which existed in previous versions as well) is that a "date" like
1/1/1850, which is obviously text, gets flagged as an existing date,
which it obviously isn't.
I'm not sure whether to treat it as an out-of-range value, e.g.:
ElseIf IsDate(v) Then
If VarType(v) = 8 Then
Err.Raise 2003 'date out of range
Else
Err.Raise 2000 'already date
End If
(which can be confusing because the same error flags 18500101) or to
generate a different error, along the lines of
Some cells contain "dates" that are actually text.
which would also flag text like '1/1/1950. I suppose that one could
use the "non-numeric" error message, but that's likely to be even more
confusing, no?
Here's another stab using an additional error message. I've also
dropped the error number in the error message box, which is only
meaningful to the coder:
Public Sub ISOToExcelDate3()
Const NUMFORMATSTR As String = "mm/dd/yyyy"
Dim cell As Range
Dim errs(0 To 5) As Boolean
Dim date04 As Boolean
Dim MaxValue As Long
Dim MinValue As Long
Dim n As Long
Dim v As Variant
Dim errDescs(0 To 5) As String
date04 = ActiveWorkbook.Date1904
errDescs(0) = "Some cells contain ""dates"" that are " & _
"actually text. They are highlighted in bold."
errDescs(1) = "Some cells contain dates already. " & _
"These are now highlighted in bold."
errDescs(2) = "Some cells contain non-numeric data. " & _
"They are highlighted in bold."
errDescs(3) = "All ISO8601:2000 dates have eight digits. " & _
"Non compliant numbers are now highlighted in bold."
errDescs(4) = "With your current date settings Excel does not " & _
"accept some of your dates. Dates before 190" & -4 * date04 _
& " are highlighted in bold."
errDescs(5) = "Excel requires dates before " & _
"31-Dec-9999. Dates after that are highlighted in bold."
MinValue = 19000101 - 4000 * date04
MaxValue = 99991231
On Error GoTo ErrorHandler
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
With cell
v = .Value
If IsEmpty(v) Then
'ignore blank cells
ElseIf IsDate(v) Then
If VarType(v) = 8 Then
Err.Raise 2000
Else
Err.Raise 2001
End If
ElseIf IsNumeric(v) = False Then
Err.Raise 2002
'or ignore text ???
ElseIf v < 10000000 Then
Err.Raise 2003
ElseIf v < MinValue Then
Err.Raise 2004
ElseIf v > MaxValue Then
Err.Raise 2005
Else
n = Fix(v)
.Value = DateSerial(n \ 10000, (n \ 100) Mod 100, _
n Mod 100)
.NumberFormat = NUMFORMATSTR
End If
End With
Next cell
On Error GoTo 0
Exit Sub
ErrorHandler:
cell.Font.FontStyle = "Bold"
n = Err.Number - 2000
If n >= 0 Then
If Not errs(n) Then
MsgBox errDescs(n)
errs(n) = True
End If
Else
'give message for any other errors, too
MsgBox "Unexpected error " & Err.Number & _
", " & Err.Description & " occurred."
End If
Err.Clear
Resume Next
End Sub
In article <tmtv4v0ln3q48ro5c...@4ax.com>, Myrna Larson
Norman Harker
I've tested the latest iteration:
My Regional Settings default for dates is d/mm/yyyy
It handles non-contiguous ranges OK
A text entry of 20030223 was converted to non-text and was not made bold
(????)
A text entry of 20030223 + space was converted to non-text and was not made
bold (????)
Pre 1900 was made bold (OK)
Already a date was made bold (OK)
9 digits was made bold (OK)
A text date entry of 02/23/2003 was not converted but was made bold (OK)
A text date entry of 02/23/2003 + space was not converted but was made bold.
(OK)
All error messages came up only once despite multiple cases (OK)
There was no error / information message regarding the conversion of text
20030223 or 20030223 + space (????)
I think that there's inconsistency here. Either text entries are to be
converted or they aren't. I really don't think that text should be treated
differently but if it is to be dealt with it must be made bold.
If the are not to be converted they should be made bold.
If they are converted they should be made bold.
On balance I think *not* converting is the best option as text entry of
numbers is a separate issue that should be addressed separately before the
conversion to Excel Date Serial Number. To convert is to impose a conversion
when users may not want it. Are we right to assume that they do want it
converted? Probably! But not certainly.
So here's amended subroutine that tests OK.
Public Sub ISOToExcelDate3()
Const NUMFORMATSTR As String = "mm/dd/yyyy"
Dim cell As Range
Dim errs(0 To 6) As Boolean
Dim date04 As Boolean
Dim MaxValue As Long
Dim MinValue As Long
Dim n As Long
Dim v As Variant
Dim errDescs(0 To 6) As String
date04 = ActiveWorkbook.Date1904
errDescs(0) = "Some cells contain ""dates"" that are " & _
"actually text. They are highlighted in bold."
errDescs(1) = "Some cells contain dates already. " & _
"These are now highlighted in bold."
errDescs(2) = "Some cells contain non-numeric data. " & _
"They are highlighted in bold."
errDescs(3) = "All ISO8601:2000 dates have eight digits. " & _
"Non compliant numbers are now highlighted in bold."
errDescs(4) = "With your current date settings Excel does not " & _
"accept some of your dates. Dates before 190" & -4 * date04 _
& " are highlighted in bold."
errDescs(5) = "Excel requires dates before " & _
"31-Dec-9999. Dates after that are highlighted in bold."
errDescs(6) = "Some of your numbers look like numbers " & _
"but are text entries. They are highlighted in bold " & _
"and have not been converted."
MinValue = 19000101 - 4000 * date04
MaxValue = 99991231
On Error GoTo ErrorHandler
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
With cell
v = .Value
If IsEmpty(v) Then
'ignore blank cells
ElseIf Application.WorksheetFunction.IsText(v) Then
Err.Raise 2006
I'm not sure whether using the Application.WorksheetFunction approach is the
best way to handle the text issue but it works OK. I've put it in out of
sequence but the sequence of ErrDescs can be changed if we have agreement
and no better approach is used.
A text entry of 20030223 was not converted and was made bold (OK)
A text entry of 20030223 + space was not converted was made bold (OK)
Pre 1900 was made bold (OK)
Already a date was made bold (OK)
9 digits was made bold (OK)
A text date entry of 02/23/2003 was not converted but was made bold (OK)
A text date entry of 02/23/2003 + space was not converted but was made bold.
(OK)
All error messages came up only once despite multiple cases (OK)
There was a new error / information message regarding the non conversion of
text 20030223 or 20030223 + space (OK)
Now for the Format of the return! We initially selected a return number
format that isn't very user friendly outside the United States. I'd prefer
dd-mmm-yyyy as that is less capable of being mis-understood by anyone. But
in an ISO context maybe we should go for the ISO8601:2000 approved separated
form of yyyy-mm-dd. It is in the limited group of three date forms of entry
recognised under all Regional Options but it suffers from having to be
'looked at' to be understood.
Myrna Larson
I've been mulling over your problem a bit more. With the present code, even after verifying that
the cell contains an 8-digit number between 19000101 and 99991231, you may still be converting
something that isn't a valid yyyymmdd date, i.e. 20030000, 20030229, 20030431, 20031961, etc,
etc.
Maybe the validation checking needs to be totally reworked. One possibility is shown below. (I
also turned off screen updating and set calculation to manual.)
However, after all of this, I wonder if we're "reinventing the wheel" here. If you simply select
the column containing dates (assumes they are arranged in column), then go to
Data/TextToColumns, select delimited, choose as the delimiter some character not found in the
data (Tab is probably OK), then on the next dialog say the Column Data Format is Date and select
YMD from the list, Excel will convert all of the VALID dates, in place. The Date system (1900 or
1904) is automatically taken into account when determining valid dates.
Of course you don't get error messages or bold font on the "bad" dates... However, you can
identify them by sorting. All of the invalid 8-digit numbers and any alpha entries will sort to
the bottom: the date serial number for Dec 31, 9999 is 2,957,003 (less than 19,000,101 or
19,040,101), and text and errors are sorted after numbers.
But this is still a bit quirky, IMO. It works OK if the original data is either 8-digit numbers
or 8-character text. BUT if there are any "real" Excel dates in the column, TextToColumns
converts them to text in the original format, i.e. the date 12/26/1955 becomes the text
"12/26/1955". Go figure...
Public Sub ISOToExcelDate4()
Dim AdjustFor1904 As Long
Dim CalcMode As Long
Dim Cell As Range
Dim D As Long
Dim N As Long
Dim M As Long
Dim MaxYr As Long
Dim MinDate As Date
Dim MinYr As Long
Dim MyErrNum As Long
Dim MyErrDescs(1 To 5) As String
Dim MyErrFlags(1 To 5) As Boolean
Dim TestDate As Date
Dim Y As Long
Const NUMFORMATSTR As String = "mm/dd/yyyy"
MaxYr = 9999
MinDate = #1/1/1900#
MinYr = 1900
AdjustFor1904 = 0
If ActiveWorkbook.Date1904 Then
MinDate = #1/1/1904#
MinYr = 1904
AdjustFor1904 = 1462
End If
MyErrDescs(1) = "Not an 8-digit number"
MyErrDescs(2) = "Year is earlier than " & Format$(MinYr)
MyErrDescs(3) = "Year is greater than 9999"
MyErrDescs(4) = "Month is invalid"
MyErrDescs(5) = "Day of month is invalid"
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
On Error GoTo ErrorHandler
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
MyErrNum = 0
If (Cell.Text Like "########" = False) Then
MyErrNum = 1 'not an 8-digit number
'if you want to analyzze the problem further, i.e. already a date,
'text, etc, you would do it here
Else
N = Fix(Cell.Value)
Y = N \ 10000
If Y < MinYr Then
MyErrNum = 2 'too early
ElseIf Y > 9999 Then
MyErrNum = 3 'too high
Else
'year is OK, but need to check month and day
N = N Mod 10000
M = N \ 100
D = N Mod 100
If M < 1 Or M > 12 Then
MyErrNum = 4 'invalid month
ElseIf D < 1 Or D > 31 Then
MyErrNum = 5 'invalid day
Else
TestDate = DateSerial(Y, M, D)
If Month(TestDate) <> M Then
MyErrNum = 5 'D > last day of month M
Else
With Cell
.Value2 = TestDate - AdjustFor1904
.NumberFormat = NUMFORMATSTR
End With
End If 'day is OK for month
End If 'check month and day
End If 'year is OK
End If 'an 8-digit number
If MyErrNum <> 0 Then
Cell.Font.FontStyle = "Bold"
If Not MyErrFlags(MyErrNum) Then
MsgBox MyErrDescs(MyErrNum)
MyErrFlags(MyErrNum) = True
End If
End If
NextCell:
Next Cell
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
Exit Sub
ErrorHandler:
'handles only *unexpected* errors
MsgBox "Unexpected error " & Err.Number & _
", " & Err.Description & " occurred."
Err.Clear
Resume NextCell
End Sub
J.E. McGimpsey
<myrna...@charter.net> wrote:
> However, after all of this, I wonder if we're "reinventing the wheel" here
Well, Data/Text to Columns *was* my original suggestion...
This is rapidly becoming a significant routine. My initial
modifications of Norman's code were mostly to play with it and perhaps
to make it a bit more efficient, not validate the design (and my
subsequent patch didn't help, particularly). So before I did anything
else on it, I'd want to go back to setting out the design
specifications...
Just off the top of my head:
1) Converts valid ISO8601:200 dates within the range 19040101 to
99991231 to valid XL dates.
2) If the XL Date system is set to 1900, converts valid ISO8601:200
dates with the range 19000301 to 19031231 to valid XL dates
3) If the XL Date system is set to 1900, converts valid ISO8601:200
dates with the range 19000101 to 19000228 to ???? (Suggestions?)
4) Converts all of the above valid ISO8601:200 dates in a selected range
5) Ignores valid XL dates in the selected range
6) Ignores blank cells in selected range
7) Ignores text entries in selected range (should text entries that can
be interpreted as dates be operated on?)
8) Informs user of numeric entries which are invalid ISO8601:200 dates.
9) Informs user of numeric entries which are valid ISO8601:200 dates,
but are outside the selected date system range.
10) Valid XL dates have a hard-coded date format applied
perhaps a few others:
a) ignores error values in selected range
b) instead of hard-coding, a user-selectable date format is applied
c) ignores cells in selected range that contain functions, not values?
d) By "Informs" mean throwing up a msgbox and bolding any affected cells
e) informs user if a range was not selected
f) converts separated values (e.g. yyyy-mm-dd)???
g) routine should run as UDF instead with sub wrapper?
Norman Harker
I had realised this problem with "impossible" dates but don't have a problem
with it any more than I have a problem with:
=DATE(2003,02,29)
returns 1-Mar-2003
OR
=DATE(2003,14,23)
returns 23-Feb-2004
All that the algorithm we use is doing is what DATE does. Myrna has noticed
that Data > Text to Columns gets horribly confused if you parse bad dates
selecting a Date option. But if you do Data > Text to Columns without using
the date option and feed the three entries into DATE you get what we are
getting. I should have mentioned that impossible day and impossible months
are in my checking matrix.
However, doing it without telling the user is another matter! That is a tad
on the arrogant side of programming. My view is that we take the previous
approaches and modify as follows:
Pan through the data without making any changes. User gets reports and
descriptions of what is intended but then gets 3 options
1. Bale out of here and leave my data alone you rats!
2. Just highlight the 'special cases' and then bale out to give me a chance
to look at them and decide on what to do.
3. Highlight and (where appropriate) do all conversions (including the
conversion of impossible dates).
I've thought out the date format problem and in the interests of
international peace I think we could give a limited number of options:
A. mm/dd/yyyy will please the US as it's their standard form
B. dd/mm/yyyy will please most UK and UK based and European users as it's
their standard form.
C. dd-mmm-yyyy will please multi-nationals as that is unequivocally
interpretable correctly by all English language speakers (including, for
avoidance of doubt, US and Australia <vbg>).
D. yyyy-mm-dd is ISO8601:2000 recommended separated format (and since we
have an ISO8601:2000 non-separated form, I suggest that this is the default.
But I'm quite prepared to accept majority of users might prefer A and dates
in this format are not so readily readable except by the Chinese who already
use that system.)
The solution of "impossible" dates might be based upon the algorithm used to
test a date we get from user inputs to separate cells of year month and day.
That method is to check:
Month returned by DATE = Month input by user (i.e. if Date has rolled the
month over because that day doesn't exist then the Month returned by date
won't have the same number as the input month, which means it was an
impossible date)
Year returned by DATE = Year input by user (i.e. ditto but done on the year)
Those are simple tests that will reveal impossible dates without too much
complexity. The problem is always the days in month but the above is how I
test a three cell year month day input by formula:
=IF(OR(MONTH(D1)<>B1,YEAR(D1)<>A1),"Doh!",0)
Apart from 29-Feb-1900 that is OK on reporting impossible dates.
Regarding your "go figure" return. I think I've seen a Knowledge Base
article on those peculiar returns.
As for screenupdating and calculation setting to manual. Yes. We could be
handling very large amounts of data here from EMR (Electronic Medical
Record) data bases and such like so....
I'd have attacked the above suggestions but JE and Myrna are less likely to
commit crimes against humanity and good programming techniques. But It might
also be best to get agreement that this is the way to go.
I'll repeat. This subroutine is likely to become a commonly required utility
so it *is* worth getting it knocked into reasonable shape. EMRs is the US
are just the start of implementation. In passing, if there is an Excel
Add-in that does the conversion it will facilitate the process of
implementation. BTW Going in the opposite direct is a lot easier!
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Myrna Larson" <myrna...@charter.net> wrote in message
news:ckq45v8duu6d8tipt...@4ax.com...
Myrna Larson
party and didn't see your original response where you suggested this.
I agree with your comments that the specifications must be laid out explicitly. Obviously Norman
wants this routine to do a better job than Text-to-Columns gives us.
Myrna Larson
20030229. I am starting with the assumption that this is data keyed-in by a person. If so, then
20030229 isn't the way you would expect him/her to enter the date March 1, 2003. I would
conclude that it is in fact a typo (was it supposed to be 20030329 or 20030228?) and should be
flagged as such.
I agree with JE that the specifications re what to do with errors should be solidified.
As for the format, if the dates are all in a single column, that's easy enough for the user to
change after running the macro. Or another macro could be written that scans an entire worksheet
and, e.g., changes cells formatted as mm/dd/yyyy to yyyy-mm-dd (or whatever the user wants).
Another option would be to include a form with a drop-down list of date formats. That approach
would avoid having to write the code to error-check a format entered into an input box. The
latter is always the most tedious and, IMO, not-fun part of writing code -- users always manage
to come up with input errors that you didn't foresee.
J.E. McGimpsey
In article <put55v8crahto1ubk...@4ax.com>, Myrna Larson
J.E. McGimpsey
the ellipsis.
I think there's value in an app like this - Text to Columns does 80% of
the job, but not 100%. If the specifications can be folded, spindled
and mutilated into something concrete (especially the UI) I'm game for
rewriting it a bit more solidly.
In article <bpt55v8tav2jvfntv...@4ax.com>, Myrna Larson
evorG nalraH
..
>Maybe the validation checking needs to be totally reworked. One possibility is
>shown below. (I also turned off screen updating and set calculation to manual.)
>Public Sub ISOToExcelDate4()
..
> MaxYr = 9999
..
Should be a constant.
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
..
Picky: should save/restore .ScreenUpdating state in case this macro were called
from another macro.
> If (Cell.Text Like "########" = False) Then
> MyErrNum = 1 'not an 8-digit number
..
Doesn't this pass along date serial values formatted as YYYYMMDD or DDMMYYYY or
any other whacko variation?
> N = Fix(Cell.Value)
> Y = N \ 10000
..
Very picky: Fix rather than Int? Less readable when only positive values will
pass validation.
> If MyErrNum <> 0 Then
> Cell.Font.FontStyle = "Bold"
> If Not MyErrFlags(MyErrNum) Then
> MsgBox MyErrDescs(MyErrNum)
> MyErrFlags(MyErrNum) = True
> End If
> End If
>
>NextCell:
> Next Cell
..
Argh! Message boxes within loops! What if the selected range has thousands of
cells with hundreds of cells that don't pass validation testing? Well, you get
hundreds of @#$%&*! dialog boxes that need to be dismissed in order for the
macro to finish. A royal PITA!
>End Sub
Using your macro as basis, here's my variation. Sorry about the lack of
indentation - I have to use a web-based news service that strips leading w/s.
Public Sub ISOToDateSerial()
Const NUMFORMATSTR As String = "mm/dd/yyyy"
Const MaxDate As Long = 99999999
Const CalcMode As Long = 1
Const SUMode As Long = 2
Dim AdjustFor1904 As Long
Dim AppStatus(CalcMode To SUMode) As Long
Dim MinDate As Long
Dim MyErrNum As Long
Dim MyErrDescs(1 To 5) As String
Dim MyErrFlags(1 To 5) As Boolean
Dim MyErrMsg As String
Dim UnexpectedErrors As String
'I can't stand long variable names for things that change frequently - so sue me
Dim c As Range 'cell iterator
Dim d As Long 'day of month
Dim m As Long 'month of year
Dim r As Date 'resulting date serial value
Dim v As Long 'truncated long integer value of cell
If ActiveWorkbook.Date1904 Then
MinDate = 19040000
AdjustFor1904 = 1462
Else
MinDate = 19000000
AdjustFor1904 = 0
End If
MyErrDescs(1) = "Not an 8-digit number" & Chr(13)
MyErrDescs(2) = "Year is earlier than " & Format(MinDate / 10000) & Chr(13)
MyErrDescs(3) = "Year is greater than 9999" & Chr(13)
MyErrDescs(4) = "Month is invalid" & Chr(13)
MyErrDescs(5) = "Day of month is invalid" & Chr(13)
AppStatus(CalcMode) = Application.Calculation
Application.Calculation = xlCalculationManual
AppStatus(SUMode) = CLng(Application.ScreenUpdating)
Application.ScreenUpdating = False
On Error GoTo ErrorHandler
For Each c In Intersect(Selection, ActiveSheet.UsedRange)
MyErrNum = 0
If IsNumeric(c.Value) Then v = Int(Val(c.Value)) Else v = -1
If Not Format(v) Like "########" Then
'date vals formatted in 8 digits fail this check, as do any other date vals
MyErrNum = 1 'not an 8-digit number
'if you want to analyzze the problem further, i.e. already a date,
'text, etc, you would do it here
ElseIf v < MinDate Then
MyErrNum = 2 'too low
ElseIf v > MaxDate Then
MyErrNum = 3 'too high
Else 'year is OK, but need to check month and day
m = Int((v Mod 10000) / 100)
d = v Mod 100
r = DateSerial(Int(v / 10000), m, d)
If Month(r) <> m Then
MyErrNum = 4 'invalid month
ElseIf Day(r) <> d Then
MyErrNum = 5 'invalid day
Else
c.Value2 = r - AdjustFor1904
c.NumberFormat = NUMFORMATSTR
End If 'check month and day
End If 'check value and year
'keep a record of cells failing validation, but DON'T display msg each time
If MyErrNum <> 0 Then
c.Font.FontStyle = "Bold"
MyErrFlags(MyErrNum) = True
MyErrDescs(MyErrNum) = MyErrDescs(MyErrNum) & c.Address(0, 0) & " "
End If
NextCell:
Next c
Application.Calculation = AppStatus(CalcMode)
Application.ScreenUpdating = CBool(AppStatus(SUMode))
Application.Calculate
'Take error messages outside the loop. Very few things in Excel are quite as
'annoying as having to click on hundreds or thousands of redundant/repetitive
'error messages.
For MyErrNum = LBound(MyErrFlags) To UBound(MyErrFlags)
If MyErrFlags(MyErrNum) Then MsgBox RTrim(MyErrDescs(MyErrNum))
Next MyErrNum
If UnexpectedErrors <> "" Then MsgBox UnexpectedErrors
Exit Sub
ErrorHandler:
'handles only *unexpected* errors
If UnexpectedErrors = "" Then UnexpectedErrors = "Unexpected Errors:"
UnexpectedErrors = UnexpectedErrors & Chr(13) & c.Address(0, 0) & _
": [" & Err.Number & "] " & Err.Description
Err.Clear
Resume NextCell
End Sub
[Munged name/e-mail address so Norman sees this - also nice to show how
ineffectual blocked sender lists are.]
--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.
J.E. McGimpsey
In article <FNR4a.13690$15....@www.newsranger.com>, evorG nalraH
<moc.loa@vrgnlrh> wrote:
> > If MyErrNum <> 0 Then
> > Cell.Font.FontStyle = "Bold"
> > If Not MyErrFlags(MyErrNum) Then
> > MsgBox MyErrDescs(MyErrNum)
> > MyErrFlags(MyErrNum) = True
> > End If
> > End If
> >
> >NextCell:
> > Next Cell
> ..
>
> Argh! Message boxes within loops! What if the selected range has thousands of
> cells with hundreds of cells that don't pass validation testing? Well, you get
> hundreds of @#$%&*! dialog boxes that need to be dismissed in order for the
> macro to finish. A royal PITA!
No you don't - you get one msgbox for each error type that exists.
That's what the
If Not MyErrFlags(myErrNum)
is for.
Harlan Grove
..
>No you don't - you get one msgbox for each error type that exists.
>That's what the
>
> If Not MyErrFlags(myErrNum)
>
>is for.
You're right.
I still like my alternative because it mentions which cells have problems, but
not essential if different validation failures produce different formatting.
Then again, maybe just add cell comments to state what's wrong.
J.E. McGimpsey
<hrl...@aol.com> wrote:
> Then again, maybe just add cell comments to state what's wrong.
then *you* get to answer all the "what are the little red triangle
thingies showing up on my sheet" posts... <g>
Not to mention those who set Tools/Options/View/Comments: None...
Personally, I'd settle for being able to return a real error code with
description that the rest of XL recognized.
Norman Harker
Sorry for the delay. I was off doing a bit of concentrated writing.
But I agree on it being better to assume data entry error for impossible
dates and that rather than resolve them, they should be highlighted for
'manual' correction. Date calculation with the ISO8601:2000 non-separated
numbers is possible but not very easy to perform so it is much more likely
to be data input error.
In passing, we will see a need to do ISO > Excel > ISO processes to
facilitate date calculation with ISO dates. I've played around with those
calculations and ISO numbers are not easy to manage.
My view on process, so as to get the code to meet need and not the other way
round:
Two options:
Convert what you can that isn't clearly or potentially an error and
highlight the ones not converted.
OR
"Refuse" to convert any if there are any actual or potential errors and
highlight the ones that are problems.
Some "errors" are a blank cells or "pure text" (i.e. text that can't be
evaluated as dates) and it might be being unnecessarily awkward if we insist
on operating on "clean data" only. So I'd go for the first option.
Error messages. I think we have them clear. JE's approach only reported a
given error once although it did trap and highlight them all.
So the only modification to the last JE approach looks like a need to add a
check for the "impossible" dates, add an appropriate error message and
highlight them without conversion.
I think that trapping might use the formula approach where we convert (in
the code) to Excel date and then check that the month is the same as the
digits 5/6 and the year is the same as digits 1-4.
On date format returned. No way in a million years that the user gets to
specify a format! That would need error trapping and it could well hit
Regional Option problems. So here our code options are:
Just return one format.
OR
Give a limited choice of formats for user to select.
If it's to be just one format I would go for dd-mmm-yyyy as it is accepted
in all Regional Options and it is unequivocally understood irrespective of
your preferred or customary format. Other candidates presume US or "Other"
country users and custom date format. ISO separated form isn't immediately
readable outside China.
Limited formats choice allows different users in different countries to get
the most common format used and I would restrict choice to just four
mm/dd/yyyy, dd/mm/yyyy, dd-mmm-yyyy, and yyyy-mm-dd. It's no big deal that
we don't give more! We've given the most common 2, the unequivocal option
and ISO mandated separated form. If they want other ones then they can still
re-format to their requirements.
I'd go for limited choice option but it is really only an "add on" to the
essential task and anyone can change the single code string easily
especially if it is placed right up the top of the code like Myrna did.
Do we have agreement?
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Myrna Larson" <myrna...@charter.net> wrote in message
news:put55v8crahto1ubk...@4ax.com...
J.E. McGimpsey
previous post:
Does the sub ignore cells with functions? (Yes)
Error handling if range not selected when called? (Yes)
Design as UDF with Sub Wrapper? Has the advantage can use it from the
worksheet (=ISOTODATE(A1)), returning standard errors. Wrapper allowing
call from button/key command and giving msgboxes/format for errors.
(Dunno)
If all the errors are flagged by BOLD it becomes hard to tell which of
three or four errors the bolding refers to. Change format for
different errors? Flag only one type of error and tell user to run
again once the errors have been corrected? (Dunno)
In article <e5xT61e2...@TK2MSFTNGP09.phx.gbl>, Norman Harker
<njha...@optusnet.com.au> wrote:
> Do we have agreement?