How to count time value in a column??

[MFH136]

Hi Everyone. I am working on production sheet where I need to use count function for date and time.
I will explain with an example.
Suppose there are 3 columns A,B,C named as "Tasks", "Date" and "Time". Whenever the operators enters "OK" in Task column, the Date and Time are automatically updated. To have this I have used the following formula for Column B "Date" =IF(A4="OK",IF(B4="",TODAY(),B4),"") and for Column C that is "Time" I have used formula as =IF(A4="OK",IF(C4="",NOW(),C4),"") which gives the time and date when the task says "OK"

Now what I need is to count how many tasks were completed on a certian date and also how many of these tasks were completed in day shift (before 06:00pm) and how many were completed in night sift (after 06:00pm).

For date I am using this formula: =COUNTIF(B4:B8,B2) where b4:b8 is the column range and b2 is the cell containing that day's date.

What formula should I use to count time?

Thank you in advance.

 

[jorismoerings]

Hi,

Could you share some sample data and explain how to prevent the circular reference with your current formula's in cells B4 and C4.
Next to that: TODAY() and NOW() will be reset on every sheet calculation performed so basically will change on all rows as soon as an operator changes 1 cell in column A to OK all cells relating to column A in which the line holds OK will be reset to current date and time.
Don't think this is what you're looking for.

 

[PaddyD]

Hi - welcome to the board!

"... which gives the time and date when the task says "OK"

Unfortunately not. It will give today() and now() for when OK is first entered in the column, but both values will continually update every time something happens on the sheet. (Try it - enter =now() in a cell somewhere, go off and do something for a second or two, then enter something else in the sheet - the now() return will update).

You'll need to use a bit of vba to get the date / time stamp entered in a static form - have a search on the board for date / time stamp. post back if you can't get it sorted.

Sorry - have to rush off so will leave the counting component to others / later.

 

[Kdburchette2]

You can use the Countifs formula to get number of task.

=Countifs(Date_Range, Date_Cell, Time_Range, "<" & Time(18,0,0)) For before six
=Countifs(Date_Range, Date_Cell, Time_Range, ">=" & Time(18,0,0)) For six and after

Using the values you gave in your example -

=Countifs(B4:B8,B2,C4:C8,"<"&Time(18,0,0)) Before Six
=Countifs(B4:B8,B2,C4:C8,">="&Time(18,0,0)) After Six

 

[jorismoerings]

@Kdburchette2

Within current example column C will hold a complete date/time serial number. So when using the COUNTIFS you will need to include the date from B2 as well or the date part from the serialnumber in Column C needs to be eliminated.

So it will look like this:
=Countifs(B4:B8,B2,C4:C8,"<"&(B2+Time(18,0,0))) Before Six
=Countifs(B4:B8,B2,C4:C8,">="&(B2+Time(18,0,0))) After Six

 

[Kdburchette2]

@Kdburchette2

Within current example column C will hold a complete date/time serial number. So when using the COUNTIFS you will need to include the date from B2 as well or the date part from the serialnumber in Column C needs to be eliminated.

So it will look like this:
=Countifs(B4:B8,B2,C4:C8,"<"&(B2+Time(18,0,0))) Before Six
=Countifs(B4:B8,B2,C4:C8,">="&(B2+Time(18,0,0))) After Six

Very true, I miss read the post and thought only time was in column C.

To get a static date and time you can paste this into the worksheet module that contains this table. When you type ok in Column A it will put the Date in column B and Date and Time in column C.

Option Compare Text


Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
   
Set Task = Range("A:A")
    
On Error Resume Next


    If Not Application.Intersect(Task, Range(Target.Address)) Is Nothing Then
        If Target.Value = "OK" Then
            ' Change Formating in this line for your desired format for column B
            Target.Offset(0, 1).Value = Format(Now(), "MM/DD/YYYY")
            ' Change Formating in this line for your desired format for column C
            Target.Offset(0, 2).Value = Format(Now(), "MM/DD/YYYY H:MM AM/PM")
        End If
    End If
End Sub

 

[jorismoerings]

@Kdburchette2,

The devil is in the details. With your VBA column C will still hold the date as your only changing the the format of the value. If it actually need just a date and a time, change it to this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
   
Set Task = Range("A:A")
    
On Error Resume Next


    If Not Application.Intersect(Task, Range(Target.Address)) Is Nothing Then
        If UCase(Target.Value) = "OK" Then
            ' Change Formating in this line for your desired format for column B
            Target.Offset(0, 1).Value = Format(Date, "MM/DD/YYYY")
            ' Change Formating in this line for your desired format for column C
            Target.Offset(0, 2).Value = Format(Time, "HH:MM AM/PM")
        End If
    End If
End Sub