determining if cell is a date or number using C#
 

How can one determine if the cell contains a number or a date using C#.
I have a column where users occassionally type the / for dates and
occassionally dont (because their ERP system accepts either format).
So the column contains a mix of values like:

9/16/05
103105

If the format is a date I want to convert from the (almost) dateserial value
otherwise I want to convert to a string and parse the month day year.
The value2 property returns double for both and I can't figure out how to
determine which format excel is using.
I tried to work around the issue using the text property to always return
the string and then I could check for the /. Unfortunatly I found that if
the column is not wide enough the text property returns #### so that wont
work either.

How do you get the current format of the cell?
Any ideas?

Thanks,

determining if cell is a date or number using C#
 

Try looking at
Selection.NumberFormat
It will return a string like "General" for numbers and something like
"mm/dd/yyyy" for dates.
--
Gary's Student


"Kurt" wrote:

How can one determine if the cell contains a number or a date using C#.
I have a column where users occassionally type the / for dates and
occassionally dont (because their ERP system accepts either format).
So the column contains a mix of values like:

9/16/05
103105

If the format is a date I want to convert from the (almost) dateserial value
otherwise I want to convert to a string and parse the month day year.
The value2 property returns double for both and I can't figure out how to
determine which format excel is using.
I tried to work around the issue using the text property to always return
the string and then I could check for the /. Unfortunatly I found that if
the column is not wide enough the text property returns #### so that wont
work either.

How do you get the current format of the cell?
Any ideas?

Thanks,

determining if cell is a date or number using C#
 

Not sure about C# but maybe either of these might lead to something

Dim v1 As Variant
v1 = ActiveCell.Value

MsgBox IsDate(v1), , v1
MsgBox VarType(v1) = vbDate, , v1

They don't always return same, if say date has been entered as a string.

Also look at CDate

Regards
Peter T


"Kurt" wrote in message
...
How can one determine if the cell contains a number or a date using C#.
I have a column where users occassionally type the / for dates and
occassionally dont (because their ERP system accepts either format).
So the column contains a mix of values like:

9/16/05
103105

If the format is a date I want to convert from the (almost) dateserial
value
otherwise I want to convert to a string and parse the month day year.
The value2 property returns double for both and I can't figure out how to
determine which format excel is using.
I tried to work around the issue using the text property to always return
the string and then I could check for the /. Unfortunatly I found that if
the column is not wide enough the text property returns #### so that wont
work either.

How do you get the current format of the cell?
Any ideas?

Thanks,

determining if cell is a date or number using C#
 

This appears to be working! I had the assumption that this was General
unless the user changed the format, but it does contain a format string for
dates. A quick search for an unescaped instance of the characters mdyhs
appears to be almost foolproof.

- Kurt


"Gary''s Student" wrote:

Try looking at
Selection.NumberFormat
It will return a string like "General" for numbers and something like
"mm/dd/yyyy" for dates.
--
Gary's Student


"Kurt" wrote:

How can one determine if the cell contains a number or a date using C#.
I have a column where users occassionally type the / for dates and
occassionally dont (because their ERP system accepts either format).
So the column contains a mix of values like:

9/16/05
103105

If the format is a date I want to convert from the (almost) dateserial value
otherwise I want to convert to a string and parse the month day year.
The value2 property returns double for both and I can't figure out how to
determine which format excel is using.
I tried to work around the issue using the text property to always return
the string and then I could check for the /. Unfortunatly I found that if
the column is not wide enough the text property returns #### so that wont
work either.

How do you get the current format of the cell?
Any ideas?

Thanks,

determining if cell is a date or number using C#
 

This works in VBA. C#, however, does not have the variant data type and
methods like VarType do not exist. When the value is marshalled it is copied
into the equivalent datatype. I'm not exactly sure why its not copied to a
DateTime as I initailly expected. Since dates are stored as floating point
numbers I'm not entirely surprised, but does seem to make it impossible to
use the value itself to determine the datatype.

- Kurt

"Peter T" wrote:

Not sure about C# but maybe either of these might lead to something

Dim v1 As Variant
v1 = ActiveCell.Value

MsgBox IsDate(v1), , v1
MsgBox VarType(v1) = vbDate, , v1

They don't always return same, if say date has been entered as a string.

Also look at CDate

Regards
Peter T


"Kurt" wrote in message
...
How can one determine if the cell contains a number or a date using C#.
I have a column where users occassionally type the / for dates and
occassionally dont (because their ERP system accepts either format).
So the column contains a mix of values like:

9/16/05
103105

If the format is a date I want to convert from the (almost) dateserial
value
otherwise I want to convert to a string and parse the month day year.
The value2 property returns double for both and I can't figure out how to
determine which format excel is using.
I tried to work around the issue using the text property to always return
the string and then I could check for the /. Unfortunatly I found that if
the column is not wide enough the text property returns #### so that wont
work either.

How do you get the current format of the cell?
Any ideas?

Thanks,