Date Comparison in Functions is Not Working

Preston Murphy

The following formula returns a value of zero (0):

=COUNTIFS({DATE ONLY}, =DATE@row, {Shift}, ="1st", {Degreaser Check Passed?}, <>"BLANK")

Here is the sheet that is being referenced:

DATE : Contains only dates or blank cells. (formula driven)

{DATE ONLY} : References a column that contains only dates or blank cells. (formula driven)

{Shift}: References a text columns that equals either "1st", "2nd", or blank. (manual entry)

{Degreaser Check Passed?} : References a text column that can contain a variety of text values or be blank. (formula driven)

How is it possible that the formula is not considering this row as a match? It seems this should be very easy, but there is something going wrong here...

The function recognizes the row as a match if the {DATE ONLY} criteria is removed.

Find more posts tagged with

Formulas

Accepted answers

Preston Murphy

RESOLUTION: If you use the following formula to extract a date from a date & time column, in order to avoid the weird time-zone mismatch issue, it will assume you are in the 1900's... not the 2000's...

=DATE(VALUE(MID([Date & Time (Automatic)]@row, 7, 2)), VALUE(LEFT([Date & Time (Automatic)]@row, 2)), VALUE(MID([Date & Time (Automatic)]@row, 4, 2)))

Use this formula instead to ensure it is properly interpreted as belonging to the 2000's

=DATE(VALUE(MID([Date & Time (Automatic)]@row, 7, 2)) + 2000, VALUE(LEFT([Date & Time (Automatic)]@row, 2)), VALUE(MID([Date & Time (Automatic)]@row, 4, 2)))

All comments

Preston Murphy

See my video here showing weird behavior when comparing two dates to each other:

https://www.loom.com/share/02982d79a41d441b8111d7bc957d2fde

If anyone can figure out what's going on here, I would be very grateful. Thank you.

Preston Murphy

=DATE(VALUE(MID([Date & Time (Automatic)]@row, 7, 2)), VALUE(LEFT([Date & Time (Automatic)]@row, 2)), VALUE(MID([Date & Time (Automatic)]@row, 4, 2)))

Use this formula instead to ensure it is properly interpreted as belonging to the 2000's

=DATE(VALUE(MID([Date & Time (Automatic)]@row, 7, 2)) + 2000, VALUE(LEFT([Date & Time (Automatic)]@row, 2)), VALUE(MID([Date & Time (Automatic)]@row, 4, 2)))

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.