Mutltiple IF(AND.... help with time stamps
Reposting from "discussions" as I think it was in the wrong place.
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 16 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?
=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")))
Answers

Try this...
=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 "08" is evaluated as zero which is less than the number 7. However, using the VALUE() function, we convert "08" into a numeric value, thus VALUE("08") > 7.
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").
 Morning (AM) hours are singledigits so we prepend "0" to the string and grab all 6digits that form the time (e.g., "011108").
 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 8:00:01 (80001) we know that it's a MIDNIGHT shift.
Help Article Resources
Categories
Check out the Formula Handbook template!