compare
 

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

--
Message posted from http://www.ExcelForum.com

compare
 

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

"rjtaylor " wrote in message
...
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 the
difference between 13:00 and 22:00 to see if it is 8 hours or
greater
thanks in advance
Rod Taylor

compare
 

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


--
Message posted from http://www.ExcelForum.com

compare
 

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

--
Message posted from http://www.ExcelForum.com