Want Tick mark and X-mark using current date and current time using Excel formula

Question

I have four tasks each day, two of which must be finished by 10 AM and the remaining two before 5:30 PM. current day and time must be used. Therefore, if the time today crosses 10 AM, I want the "tick" mark symbol to be displayed, and if not, the "X" mark symbol. This has to continue for a week.

Therefore, if my Excel document is open at all times, it should be updated in accordance with the aforementioned circumstance.

Answer 1

I likewise use a checkmark to indicate a work is finished, but I also include a time stamp many columns over. By clicking on a cell in Column B or C, my modified script inserts an "a" using a Marlett font, which creates a checkmark if before 10 am, otherwise, an "r" creates an X.

Perhaps this can be changed to fit your needs by adding a new section for 17:30. Since VBA is "dynamic" on cell changes, I prefer using it over a formula (just clicking in the cell). The check mark or X is removed by clicking in and out of the same cell once again.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 'Mark Task completed if finished before 10 am

  If Target.Cells.Count > 1 Then Exit Sub

    'Only looking at column B & C, rows 3 thru 31
    If Not Intersect(Target, Range("B3:C31")) Is Nothing Then

        Target.Font.Name = "Marlett"

            If Target = vbNullString Then
               If Time() < "10:00:00" Then
                   Target = "a"
                Else
                   Target = "r
                End If 
            Else

                Target = vbNullString


            End If
      End If
End Sub`

Hope this helps you.