comparing times
 

i would like to flag certain cells based on the criteria below,
if the time in column A is between 5AM and 5PM and the word in column B is
False, i would like the cell in B marked red.
if the time in column A is between 5PM and 5AM and the word in column B is
True i would like the cell in B marked as red.

here is what i have so far:
If (sig = "TRUE" And shouldNotSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'after hours, no
sig needed
End If
If (sig = "FALSE" And shouldSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'normal hours,
sig needed
End If

and the functions:
Function shouldSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time am And time < pm) Then
shouldSigBool = True
Else
shouldSigBool = False
End If
End Function

Function shouldNotSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time < am And time pm) Then
shouldNotSigBool = True
Else
shouldNotSigBool = False
End If
End Function

from my sample data it looks as if the first conditional is completing,
however the second is not. can anyone spot a flaw in the logic? or is there
a beter way to do this?(i dont think an OR will work). unfortunatley i dont
know anyone else with any programming experience to bug with all my issues,
and it is terribly difficult to proofread my own work.

tia
bst

comparing times
 

Change "And" to "Or" (and decide what to do if the time is EXACTLY 5am or
5pm, i.e. choose to use "<=", "=" in one of your two functions. Look at
your logic carefully.)

Function shouldNotSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time < am Or time pm) Then
shouldNotSigBool = True
Else
shouldNotSigBool = False
End If
End Function



"bst" wrote:

i would like to flag certain cells based on the criteria below,
if the time in column A is between 5AM and 5PM and the word in column B is
False, i would like the cell in B marked red.
if the time in column A is between 5PM and 5AM and the word in column B is
True i would like the cell in B marked as red.

here is what i have so far:
If (sig = "TRUE" And shouldNotSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'after hours, no
sig needed
End If
If (sig = "FALSE" And shouldSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'normal hours,
sig needed
End If

and the functions:
Function shouldSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time am And time < pm) Then
shouldSigBool = True
Else
shouldSigBool = False
End If
End Function

Function shouldNotSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time < am And time pm) Then
shouldNotSigBool = True
Else
shouldNotSigBool = False
End If
End Function

from my sample data it looks as if the first conditional is completing,
however the second is not. can anyone spot a flaw in the logic? or is there
a beter way to do this?(i dont think an OR will work). unfortunatley i dont
know anyone else with any programming experience to bug with all my issues,
and it is terribly difficult to proofread my own work.

tia
bst

comparing times
 

Doh! You are right. I don't know why i was fighting against using an OR
in my head.

thanks
bst

?B?Q2hhcmxpZQ==?= wrote in
:

Change "And" to "Or" (and decide what to do if the time is EXACTLY 5am
or 5pm, i.e. choose to use "<=", "=" in one of your two functions.
Look at your logic carefully.)

Function shouldNotSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time < am Or time pm) Then
shouldNotSigBool = True
Else
shouldNotSigBool = False
End If
End Function



"bst" wrote:

i would like to flag certain cells based on the criteria below,
if the time in column A is between 5AM and 5PM and the word in column
B is False, i would like the cell in B marked red.
if the time in column A is between 5PM and 5AM and the word in column
B is True i would like the cell in B marked as red.

here is what i have so far:
If (sig = "TRUE" And shouldNotSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'after
hours, no
sig needed
End If
If (sig = "FALSE" And shouldSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'normal
hours,
sig needed
End If

and the functions:
Function shouldSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time am And time < pm) Then
shouldSigBool = True
Else
shouldSigBool = False
End If
End Function

Function shouldNotSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time < am And time pm) Then
shouldNotSigBool = True
Else
shouldNotSigBool = False
End If
End Function

from my sample data it looks as if the first conditional is
completing, however the second is not. can anyone spot a flaw in the
logic? or is there a beter way to do this?(i dont think an OR will
work). unfortunatley i dont know anyone else with any programming
experience to bug with all my issues, and it is terribly difficult to
proofread my own work.

tia
bst