You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I’m trying to make a row that will tell me how many openers (Start Time: 9:50AM) and closers (End Time: 10:05PM) I have per day.
I don’t know what formula to put into cell C20 as everything I’ve tried either returns “0” which isn’t right, or an error.
Somebody help pls
From the screenshot it's hard to see exactly what you have in the cells.
Are they formatted as Date & Time?
If so their value includes both date and time even though you are only displaying the time. When you put "9:50AM" in the formula Numbers may be supplying today's date whereas you may have different (undisplayed) dates in the cells, so Numbers can't find a match and returns a count of 0.
The easiest fix may be to format the cells as Text and then type 9:50AM, 4:00PM , etc.
Numbers will still happily perform date and time math on the cells even though you have entered the times as text.
SG
I think SG nailed it - the shift start/end fields include an inherent date, which is breaking the COUNTIF() logic.
An alternative to using text fields would be to tell Numbers to focus ONLY on the Time component of the cell, ignoring the date. There are a couple of ways of doing this.
The easiest (conceptually) is a combination of COUNTMATCHES() and TEXTJOIN().
TEXTJOIN() takes an array of cells and returns a single text object of the results. The nice part here is that it takes the formatted value of the cells (e.g. "3:50PM") and not the underlying date/time object.
You can then run this through COUNTMATCHES() to find how many times a given string appears.
In this case, set cell C20 to:
=COUNTMATCHES(TEXTJOIN(",",TRUE,C2:C19),"9:50")
This will take the range C2:C19 and convert it to a single text string which it then compares to "9:50", returning the number of matches.
I love you, thank you so much for the detailed solution. This was exactly my problem and you solved it perfectly.
What Numbers formula should I use to count the number of employees per shift?
