Like operator

[ccordner]

I have a series of cells, all displaying times, some of which are formatted to display in the 24 hour clock ("##:##") and some of which are formatted to appear as "##h##". This is to differentiate between actual times of the day and lengths of shifts.

Is there an easy way of using vba to work out which is which?

I had been using "if .... like "#h#" but that doesn't work, because it's only the format of the cell, not the actual number.

Any ideas?

 

[GTO]

..and some of which are formatted to appear as "##h##". This is to differentiate between actual times of the day and lengths of shifts...

So "##h##" still refers to a valid time in the formula bar? If I got that correct, you may want to try Cell.Text rather than .Value.

 

[ccordner]

Thanks, that's kind of working.

The cell I'm referring to contains a reference to another worksheet. Well, in some cases. Sometimes it contains an actual value but this needs to be able to work with either.

I've put in a msgbox(cells(rownumber+3,3).text) and that displays "4h00".

Yet for some reason, neither the statements 'like "#h#"' nor 'like "#[h]#"' seem to work?

 

[GTO]

..Yet for some reason, neither the statements 'like "#h#"' nor 'like "#[h]#"' seem to work?

'#h#' would match '4h0' but not '4h00' when used in Like. The '#' matches exactly one digit, not one-to-many.

Could you show some sample data variations?

 

[ccordner]

Certainly, although you've just given me the answer.

The shifts would never be more than 12 hours, so something like 4h00, 8h45, 12h00 are the kind of results.

Therefore, like "#h##" or "##h##" should do the trick.

Chris.

 

[GTO]

Super cool, its always nice when it works out quick