Expected behavior for DATEONLY when embedded in other functions

I feel like the formula below should function to remove the timestamp off of the cell in created at the same row as the function, then count all cells in the created column in which the days are the same.

=countifs(created:created,dateonly(created@row))

Currently this formula counts when the date AND time match. In fact, when trying to force it to go through with

=countifs(created:created,date(year(created@row),month(created@row),day(created@row)))

it still only returns the values when the date AND time match.

Does anyone have a reason why either of these shouldn't work? Is this expected behavior or a glitch?

Find more posts tagged with

Accepted answers

Paul Newcome

That is certainly odd that it works for me and not for you. Have you tried it on a different sheet to see if maybe there is just a glitch in that particular sheet?

All comments

Paul Newcome

Try this...

=COUNTIFS(Created:Created, DATEONLY(@cell) = DATEONLY(Created@row))

L_123

Yes, that works Paul, I can also use

=countifs(created:created,left(@cell,8) = left(created@row,8))

Which is how I answered the question a user on here posted when they ran into this issue.

I just am trying to understand how the timestamp is carried over when both of the formula in my question have no reference to time.

Paul Newcome

Ah. Ok. I think it is going to end up being similar to when you want to count dates based on a month or year number.

You specify the range

[Date Column]:[Date Column]

Then tell the formula to pull the month

[Date Column]:[Date Column], MONTH(@cell) = #

I think it is the same concept. You enter your range of the Created column then have to tell it WHAT to compare

DATEONLY(@cell) = xxxxxxxxx

L_123

Right. And dateonly shouldn't have a time stamp, but the formula still only returns when the timestamp matches. Dateonly exclusively exists for removing the time stamp from created and modified columns, but the timestamp is still checked when using it to compare against cells that also have a timestamp.

I also tried your solution from your first response and it returned an "#Invalid Data Type" response.

Paul Newcome

What formula(s) do you have in your above screenshot that are generating the 4, 7, 5, and 5?

Invalid data type? ~~That almost sounds like a bit of a glitch~~ unless it is picking up those 10 empty rows at the bottom of the sheet. I tested this real quick, and it is picking up the empty rows. You need an IFERROR to account for those and it will work.

=COUNTIFS(Created:Created, IFERROR(DATEONLY(@cell), "") = DATEONLY(Created@row))

I've been trying to think of another way to explain my thought process behind it, and I think I might have figured out a better way to put it into words. Haha.

Saying

=countifs(created:created,dateonly(created@row))

is the same as saying

=COUNTIFS([Date Type Column]:[Date Type Column], YEAR([Date Type Column]@row))

Assuming your date column is all dates, you aren't going to have a cell that equals 2020 (for example). Similar to how if you have date/time stamps, there won't be a cell that equals just a date.

That still doesn't explain why exactly this:

=countifs(created:created,date(year(created@row),month(created@row),day(created@row)))

would count any rows at all. Using the above logic, it should return a count of zero.

And I tried testing this:

=COUNTIFS(Created:Created, DATEONLY(Created@row))

It actually worked the way you described how you thought it should work. It counted all of the same date regardless of the time stamp.

L_123

It is working correctly in your sheet because you only have one date and timestamp for april 30th. If you had more timestamps on the same day it wouldn't, try using that formula for may 7th on the same sheet, where you have 2 different timestamps. for 9:52 you would get a result of 7, and for 11:15 you would get a result of 2. See the picture below. For some reason it is keeping the time as a part of the criteria despite it not being a part of the equation.

Paul Newcome

I moved the formula so it is looking at a date that has multiple time stamps for the same date, and it is counting all of them and not just the rows that have the same time stamp.

L_123

What is going on with this.... exact same formula with different results.

https://app.smartsheet.com/b/publish?EQBCT=26320c9228b449e98c133ace4bf68a68

Paul Newcome

That is certainly odd that it works for me and not for you. Have you tried it on a different sheet to see if maybe there is just a glitch in that particular sheet?

L_123

Yeah, it is sheet specific. That's really annoying but i guess it is proof it's a glitch not a lack of functionality.

Paul Newcome

Annoying for sure, but at least it does work as expected (most of the time). Haha

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.