PDA

View Full Version : Time Tracking with value of 1 or zero in column 0900 to column 0800



atzdgreat
12-18-2019, 02:16 AM
i made a tracking tracking in excel worksheet using this formula


=IF(AND(ISNUMBER($C7),ISNUMBER($F7),ISNUMBER($G7),IF($F7<$G7,AND(I$5>=TIME(HOUR($F7),0,0),I$5<=$G7),OR(I$5>=TIME(HOUR($F7),0,0),I$5<=$G7))),1,FALSE)


values in column 0900 to column 0800 are working properly.
now that i am planning to convert it to vba code since i am now using userform and listview. From IsNumber, i changed it to IsDate since my value is date and istime for GetTimeIn and GetTimeOut



If IsDate(RngData(i, 1)) = True And IsTime(TimeValue(GetTimeIn)) = True And IsTime(TimeValue(GetTimeOut)) = True Then
If GetTimeIn < GetTimeOut Then
If TimeValue("09:00") >= TimeValue(Hour(GetTimeIn) & ":00") And TimeValue("09:00") <= TimeValue(GetTimeOut) Then
li.SubItems(7) = 1
ElseIf TimeValue("09:00") >= TimeValue(Hour(GetTimeIn) & ":00") Or TimeValue("09:00") <= TimeValue(GetTimeOut) Then
li.SubItems(7) = 1
Else
li.SubItems(7) = 0
End If

'Continue until TimeValue("08:00")
End If
End If



25649

my hourly column value are not working properly. did i miss something with my my converion from formula to vba code?