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?
=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?