Countif  Date and Time
I am trying to separate entries for day shift (8am4pm), afternoon shift (4pmmidnight) 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?
Answers

=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")))

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 (nonnumeric 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 singledigits so we prepend "0" to the string and grab all 6digits 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.

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"))


NEVERMIND GOT IT!! Amazing help as always!! I would have never gotten this one!
Thank you so much!!!
Help Article Resources
Categories
Check out the Formula Handbook template!