determining if cell is a date or number using C#

[Kurt]

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,

[Gary''s Student]

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,

[Peter T]

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" <[email protected]> wrote in message
news:[email protected]...
> 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,

[Kurt]

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,

[Kurt]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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,
>
>
>