Countif - Date and Time

Elizabeth Aird
edited 01/10/23 in Formulas and Functions

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?


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

  • Toufong Vang
    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.


  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!