Countif - Date and Time

Elizabeth Aird

I am trying to separate entries for day shift (8am-4pm), afternoon shift (4pm-midnight) and night shift (midnight -8am). I can countif for the date entries. But because the time from an automated report is "funky" I can't get it to work. So I added a column to state IF the processed time is greater than 8 and less than 4 state "Day".... I tried putting in the same format as the time and that did not work. Help? Is there a way to convert this time to a format that works?

Elizabeth Aird

=IF(AND([Processed Time]@row > 0, [Processed Time]@row < 8), "NIGHTS", IF(AND([Processed Time]@row >= 8, [Processed Time]@row < 16), "DAYS", IF(AND([Processed Time]@row >= 16, [Processed Time]@row < 23), "AFTERNOONS")))

So the first part works but nothing else.. but not getting an error message anymore! Just needs something I am missing here. It says nights when it should say afternoons, and blank for "days"...

=IF(AND([Processed Time]@row > 0, [Processed Time]@row < 8), "NIGHTS", IF(AND([Processed Time]@row >= 8, [Processed Time]@row < 16), "DAYS", IF(AND([Processed Time]@row >= 16, [Processed Time]@row < 23), "AFTERNOONS")))

Toufong Vang

@Elizabeth Aird

Hi, Elizabeth, give this a try...

=IF(VALUE(RIGHT("0" + SUBSTITUTE([Processed Time]@row, ":", ""), 6)) < 80001, "MIDNIGHT", IF(VALUE(RIGHT("0" + SUBSTITUTE([Processed Time]@row, ":", ""), 6)) < 160001, "DAY", "AFTERNOON"))

Explanation

The values in the column "Processed Time" are string values (non-numeric and equal to "0") so you need to convert them into numeric values before comparing them to numbers.

For example, the string "9:00:06" is evaluated as zero which is NOT GREATER THAN the numeric value 0. (It's equal to 0.). You need a way to convert "9:00:06" into a numeric value so that you can compare it to numbers. For this...

The long formula above...

• Substitutes all of the colons ":" in the string with nothing "". (E.g., "17:12:46" becomes "171246", "1:11:08" becomes "11108", "9:00:06" becomes "90006").
• Morning (AM) hours are single-digits so we pre-pend "0" to the string and grab all 6-digits that form the time (e.g., "171246", "011108", "090006").
• We then take the value, VALUE(), of that string of numbers and compare it with the shift's numeric value to determine the shift. For example, the midnight shift ends at 8:00:00 (80000) so if a "Processed Time" is less than 80001 we know that it's a MIDNIGHT shift.

Elizabeth Aird

Complicated! but something still isn't working for days and afternoons...

=IF(VALUE(RIGHT("0" + SUBSTITUTE([Processed Time]@row, ":", ""), 6)) < 80001, "MIDNIGHT", IF(VALUE(RIGHT("0" + SUBSTITUTE(":", ""), 6)) < 160001, "DAY", "AFTERNOON"))

Elizabeth Aird

Elizabeth Aird

NEVERMIND GOT IT!! Amazing help as always!! I would have never gotten this one!

Thank you so much!!!