Quick If Then for Times

[sassriverrat]

Hey Guys, quick question.

Cell R17 is a time value in hours, input by the user as "HH.h" meaning its hours and a decimal (5.5 hours for example).

Cell D8 is a whole number (hours) (5 hours)
Cell F8 is a whole number (minutes) (30 minutes)

I'd like a quick IF Then that says IF R17 > (D8 + (F8/60)) Then
N10 = "Error"


Here's what I have, but it doesn't work:

If Range("R17") > (Range("D8") + (Range("F8") / 60)) Then
   Range("N10").FormulaR1C1 = "=Error"
End If

 

[FormR]

but it doesn't work

Hi, that doesn't give us much to go on, but maybe..

If Range("R17").Value > (Range("D8").Value + (Range("F8").Value / 60)) Then
   Range("N10").Value = "Error"
End If

 

[sassriverrat]

Ok Trying that now-

one side question- I have a function I found (converts military time to an excel serial- see code below). I have a userform textbox that inputs data into a cell sheet (also see second code below)- is there any way to run the function so that inputting 1220 into the text box will input 12:20 (i can format the time using a cell format piece later). Basically how to run a function instead of what I have.

thanks and I'll let you know in a second what your piece did.

[FONT=&quot]Public Function MilitaryToTime(T1 As Integer)[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50[/FONT]
[FONT=&quot]' Output, time as serial time e.g, 0.5 for noon.[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]Dim TT1 As Double[/FONT]

[FONT=&quot]TT1 = Int(T1 / 100) + (((T1 / 100) - Int(T1 / 100)) / 0.6) '23.50[/FONT]
[FONT=&quot]TT1 = TT1 / 24[/FONT]
[FONT=&quot]MilitaryToTime = TT1[/FONT]

[FONT=&quot]End Function

Private Sub TextBox3_Change()
Sheets("Voyage").Range("C6").Value = TextBox3.Value
'I tried to do a .Range("C6").Function(MilitaryToTime) but that doesn't go correctly
'still learning excel!
End Sub

[/FONT]

 

[sassriverrat]

Ok tried your code- No Go

So Full code is below for the cell that it supports.

'Heavy Weather Time    WS_Count = ActiveWorkbook.Worksheets.Count
    Eqat1 = "="
    nooncnt = 0
        For i = 1 To WS_Count
             Tname = ActiveWorkbook.Worksheets(i).Name
            If Left(Tname, 4) = "Noon" Then
             Eqat1 = Eqat1 & "+" & Tname & "!N12"
             nooncnt = nooncnt + 1
            End If
        Next i
        If nooncnt > 0 Then
        Range("N10").Formula = Eqat1 & "+R17"
    End If
    If Range("R17").Value > (Range("D8").Value + (Range("F8").Value / 60)) Then
        Range("N10").Value = "Error"
    End If
    Range("N27").FormulaR1C1 = "=R[-17]C"
    Range("N44").FormulaR1C1 = "=R[-17]C"

 

[FormR]

No Go

Hi, that is not enough information to help you debug - please try to elaborate.

 

[sassriverrat]

runtime error 13
type mismatch

'Heavy Weather Time    WS_Count = ActiveWorkbook.Worksheets.Count
    Eqat1 = "="
    nooncnt = 0
        For i = 1 To WS_Count
             Tname = ActiveWorkbook.Worksheets(i).Name
            If Left(Tname, 4) = "Noon" Then
             Eqat1 = Eqat1 & "+" & Tname & "!N12"
             nooncnt = nooncnt + 1
            End If
        Next i
        If nooncnt > 0 Then
        Range("N10").Formula = Eqat1 & "+R17"
    End If
    If Range("R17").Value > (Range("D8").Value + (Range("F8").Value / 60)) Then
        Range("N10").Value = "Error"
    End If
    Range("N27").FormulaR1C1 = "=R[-17]C"
    Range("N44").FormulaR1C1 = "=R[-17]C

 

[FormR]

What is in cells R17, D8 and F8 of the active sheet when the error occurs? It's likely either D8 or F8 isn't a number.

 

[sassriverrat]

R17 is an input cell for a number
D8 is a number once the user starts inputting data into the worksheet inputs, but when the sheet is first generated, it's just "#value !" while it's waiting for userinput

So I guess that #value ! is probably kicking the error, right? How would I work around this?

 

[FormR]

So I guess that #value ! is probably kicking the error, right?

Yep, I would think so.

How would I work around this?

Difficult to say, you could possibly check that D8 is number before executing those lines, something like..

If IsNumeric(Range("D8").Value) Then
    If Range("R17").Value > (Range("D8").Value + (Range("F8").Value / 60)) Then
        Range("N10").Value = "Error"
    End If
End If

 

[sassriverrat]

You sir, are a genius. Worked like a champ!

thanks!