Conditional Formatting for Time Entries

Question

Hello! We are building a timesheet for a governmental agency. The old timesheets calculated time as percentages of an hour [i.e., 1 hour 15 minutes was recorded as 1.25] The new timekeeping system wants staff to enter time in minutes, rounded to the nearest quarter hour [i.e., 1 hour 15 minutes recorded as: 1:15, with a colon]. Change is hard - so we want the entry to conditionally format anything that is not entered in the new standard. How can we build the formula such that cells containing entries in the old format are "highlighted". The formula would read something like: if last two digits of the entry are not "00" or "15" or "30" or "45" turn the cell orange! Thank you!

Answer 1

Hi Kerri,

Youv'e probabley sorted it out by now but here a couple of macros the fisrt entered in the  sheet module (right-click sheet tab, select view code and paste in.

Private Sub Worksheet_Change(ByVal Target As Range)

    Set vRange = Range("input2")

    If Intersect(Target, vRange) Is Nothing Then

        Exit Sub

    Else

        Application.EnableEvents = False

        Target.Value = Round(Target * 96, 0) / 96

    Application.EnableEvents = True

End Sub

This will round an entry in time format, with a colon to the nearest quarter of an hour.  You will have to set the range Input2 in the sheet.

The second assumes that you want to enter the hours as a decimal value e.g. enter 1:27 as 1.27; 1:30 as 1.3

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myMins As Double, myHrs As Integer, myTime As Variant

    Set vRange = Range("input2")

    If Intersect(Target, vRange) Is Nothing Then

        Exit Sub

       Application.EnableEvents = False

       myHrs = Int(Target)

       myMins = (Target - myHrs) * 100 + myHrs * 60

       myTime = Round((myMins / 1440) * 96, 0) / 96

       Target.Value = Format(myTime, "hh:mm")

      End if

    Application.EnableEvents = True

End Sub

You will have to decide which one is suitable they do not work together.

HTH

Peter

Answer 2

Hi,

Try this formula in conditional formatting

=MOD(MINUTE(B2/24),15)<>0

Choose the format as Orange.

Hope this helps.

Answer 3

Use this formua in conditional formatting

=MOD(RIGHT(A1,2),15)<>0

then select your format