Date Comparison in Functions is Not Working

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.

Tags:

Best Answer

  • Preston Murphy
    Answer ✓

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

Answers

  • Preston Murphy
    edited 06/15/21

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

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

  • Preston Murphy
    Answer ✓

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