Countif - Date and Time
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")))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!