compare

  • Thread starter Thread starter rjtaylor
  • Start date Start date
R

rjtaylor

I think I was not asking the right question on my earlier post

I usually have a cell with a value of
1 or 12* or 7*

However sometime I might have on like this
CE 13:00-22:00

I need to know how to look for this and if encountered then check th
difference between 13:00 and 22:00 to see if it is 8 hours o
greater

thanks in advance
Rod Taylo
 
Rod,

'Assumes there is a single hyphen and
'a single space in cell if looking for time value.

'The code looks for a hyphen in cell text and if found
'then looks for the first space. It then converts
'the first 5 characters (after space) to a date and the
'last five characters in the cell to a date and
'subtracts one from the other...

'This code makes too many assumptions for me to be
'comfortable with it, but it will get you started.

'-----------------------
Sub FindTheDuration()
Dim lngSpace As Long
Dim strValue As String
Dim dteAmount As Date
Const STR_HYPHEN As String = "-"
Const STR_SPACE As String = " "

strValue = Range("D5").Value

If InStr(1, strValue, STR_HYPHEN, vbTextCompare) > 0 Then

lngSpace = InStr(1, strValue, STR_SPACE, vbTextCompare) + 1
dteAmount = CDate(Right(strValue, 5)) - CDate(Mid(strValue, lngSpace, 5))

MsgBox CDbl(dteAmount) * 24
End If
End Sub
'-----------------------

Regards,
Jim Cone
San Francisco, CA
 
Ro

tr
=IF(LEFT(A5,2)="CE", IF(HOUR(RIGHT(A5,5)-MID(A5,4,5))>=8,"More","Less"),"whatever you do otherwise"

Ton

----- rjtaylor > wrote: ----

I think I was not asking the right question on my earlier pos

I usually have a cell with a value of
1 or 12* or 7

However sometime I might have on like thi
CE 13:00-22:0

I need to know how to look for this and if encountered then check th
difference between 13:00 and 22:00 to see if it is 8 hours o
greate

thanks in advanc
Rod Taylo
 
Thanks Jim That seems to work now I need to get it into my code
I dont understand the

MsgBox CDbl(dteAmount) * 24

So I added this to put the answer back into code

If CDbl(dteAmount) * 24 >= 8 Then
MsgBox ("yes")
Else
MsgBox ("no")
End If

I also might play with the code you gave Tony thank
 
Back
Top