Mutltiple IF(AND.... help with time stamps

Elizabeth Aird

Re-posting from "discussions" as I think it was in the wrong place.

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

TVang

@Elizabeth Aird

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 (non-numeric 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 single-digits so we pre-pend "0" to the string and grab all 6-digits 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.