Using InStr on a Time Cell 2

[hankm3]

Hello,

I'm trying to use InStr on a Active cell that Contains a TimeStamp. I'm looking for the First position of a Colon. ":"

Dim Ti As Variant
Dim C As Integer

Ti = ActiveCell.Value()

C = Instr(1, Ti, ":", 1)

MsgBox (C)

Thanks

 

[JAG14]

Hank

Using the above method, InStr will return a "3" because you are asking it for the position of the first colon in the string.
This is assuming of course that your time stamp follows the same format as the VB [tt]Time[/tt] Function.
Is this what you are trying to do?

[tt]"Very funny, Scotty... Now Beam down my clothes."[/tt]

 

[hankm3]

Jag,

I agree it should but it returns 0. I tried with a Timestamp of 10:13:00 AM and a cell with 10:01 and it always returns 0. HA:: returns 3.

Thanks

 

[JAG14]

OK, try

[tt]
Dim Ti As Variant
Dim C As Integer

Ti = Str(ActiveCell.Value())

C = Instr(1, Ti, ":", 1)

MsgBox (C)
[/tt]

Maybe...

[tt]"Very funny, Scotty... Now Beam down my clothes."[/tt]

 

[AkutaSame]

Just a thought, but perhaps it's the type of content. Your active cell may contain a datestamp, but instr uses a string inside of a string. Perhaps try using this:


Dim Ti As Variant
Dim C As Integer

Ti = ActiveCell.Value()

C = Instr(1, Str(Ti), ":", 1)

MsgBox (C)


Maybe forcing it into a string form will fix your problem. I haven't tested the solution, so let us know if it works.

 

[hankm3]

Jag,

Still returns 0. Seems it will not work properly on Timestamps or Cells with leading digits.

Will keep trying.

Thanks

 

[AkutaSame]

After thinking about it (after Jag and I posted at the same time), and looking at the situation some more. The Str function won't do what you are looking for. It requires a number, not a variant. Give THIS a try:


Dim Ti As Variant
Dim C As Integer

Ti = ActiveCell.Value()

C = Instr(1, CStr(Ti), ":", 1)

MsgBox (C)

 

[JAG14]

Akutasame, Crossposting.. damn.. lol

[tt]"Very funny, Scotty... Now Beam down my clothes."[/tt]

 

[JAG14]

Caught the cstr one in time tho!

[tt]"Very funny, Scotty... Now Beam down my clothes."[/tt]

 

[hankm3]

Same Results.

My attempt is to be able to check a cell and determine it's data type. ( Text, Numeric, Date, Time, Etc)

Thanks

 

[strongm]

So what you really want is:
[tt]
MsgBox TypeName(ActiveCell.Value)

 

[mrmover]

I think you problem is in the 1 you put at the end, Try this...
Dim Ti As Variant
Dim C As Integer

Ti = ActiveCell.Value()

C = Instr(1, CStr(Ti), ":&quot

MsgBox (C)

The 1 at the end is telling it to only go for 1 char then stop, if you leave it blank it will look at the whole string
HOpe this helps!

 

[hankm3]

Same Results....

I'm Using Excel 97 on a Win 98 SE and Win 2000 Pro Machine.

If that's any help..

Also the TypeName Command returns a Double on a Timestamp.

Go Figure..

Hank

 

[strongm]

Go figure? Sure. A time value is a double, it's just formatted to appear as a time. So:

if TypeName(ActiveCell.Value)="Double" and ActiveCell.NumberFormat="h:mm:ss" then msgbox "Time"

 

[strongm]

Alternatively, if you still prefer the INstr idea:

Ti = format(ActiveCell.Value(), Activecell.NumberFormat)
C = Instr(1, CStr(Ti), ":&quot
msgbox c

 

[johnwm]

In Excel a Time value is stored as a Double representing the fraction of the day since midnight. If you use the formula =Now() in a cell it will give you a Date type. Otherwise any date value will be converted to a Double (unless you choose to format the cell otherwise)

If you really want to find the Colon position, use cdate function on the Double - that will however convert any Double into a Date (providing it's within the Date range) so you haven't gained much!

The overall answer is that Excel will always store a date as a double - it's only the format of the cell that determines how it appears

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[hankm3]

Strongm,

Your solution worked fine. Learned quite a bit from this thread and the input from all.

Thanks

 

[AndyWatt]

hankm3

If this thread has been so educational and informative don't you think awarding some stars to the people who've helped you would be appropriate?

Unless, of course, two mouse clicks are way too much effort.

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"A computer program does what you tell it to do, not what you want it to do." -- Greer's Third Law

 

[johnwm]

Thank you hankm3

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[strongm]

Woohoo!