Formula to determine if created (Date) column matches a date column or is today

Cpatt

I have a form my team uses to record several instrument calibrations, with an auto generated created date for each entry. Is there a way to compare the created date to a date column in another sheet to see if the 2 columns match? Or maybe determine if the date in the created date column is today? I think the format of the created date column is not allowing any of the formulas I have tried to work.

The instruments are supposed to be calibrated every week day, and I am trying to figure out a way to generate an alert if a day gets missed.

Right now I have a report that pulls all the calibrations completed, filtered for today, that I then manually compare to another sheet where I generated all the weekday dates, to check off that each instrument was calibrated every day.

Jeff Reisman

@Cpatt

Let me see if I can tell what you're trying to do.

IF the count of dates in the remote date range where the date equals the date in the daily verification Due Date cell on this row is greater than 0, set the cell to Calibrated, otherwise set it to Not Calibrated.

Correct?

I also encountered the invalid data type error.

In your source sheet, can you add a helper Date-type column to convert the automated Created date into the DATEONLY format? Just use

=DATEONLY(Created@row)

and make it a column formula. Then reference that helper column in your formula on the other sheet:

=IF(COUNTIF({RD BAL 001 (Large balance) Helper Date}, [Daily Verification Due Date]@row) > 0, "Calibrated", "Not calibrated")

(I tested this exact formula and it worked!)

Jeff Reisman

You can wrap the DATEONLY function around the created date so as to compare it to other manual or calculated date fields.

For example: =IF(DATEONLY(Created@row) = TODAY(0), "YES", "NO")

Cpatt

Hi,

So I got a formula to work for this by using the DATEONLY (thank you Jeff!), but I want to drag the formula down a column and have it keep comparing the next cell down in each sheet. When I drag down my formula, it keeps the reference from the other sheet on the same cell, instead of moving to the next cell down. I tried changing my reference to the column instead of an individual cell, and adding "@row" instead of "@cell" but then the formula is invalid. What am I doing wrong?

Below is my formula. The only way I can get it to work is if I change the reference on each cell manually. So like I can change the reference for the next cell down to {RD BAL 001 (Large balance) Range 2} and choose the next cell down manually in the referenced sheet, but is there a way to do this automatically?

=IF(COUNTIFS((DATEONLY({RD BAL 001 (Large balance) Range 1})), @cell = [Daily Verification Due Date]@row) > 0, "Calibrated", "Not calibrated")

Hopefully this makes sense, its Friday and my brain is fried!

Thanks!

Kelly Moore

Hey @Cpatt

Use the Convert to Column Formula found in the cell menu of a cell already containing the desired formula. Please note to edit a column formula at a later date you must go back into the cell menu and select Edit column formula.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Cpatt

@Kelly Moore - Thank you, unfortunately this did not exactly work. I think something is wrong with my reference to the other sheet.

So my formula is =IF(COUNTIFS((DATEONLY({RD BAL 001 (Large balance) Range 1})), @cell = [Daily Verification Due Date]@row) > 0, "Calibrated", "Not calibrated"

I think my issue is this part of the formula: ({RD BAL 001 (Large balance) Range 1})), @cell

I want to compare the entire column of dates in my reference sheet to a column of dates in my current sheet. But when I try to reference the column in my reference sheet it returns an error. I don't completely understand how to use the @row & @cell references in Smartsheets and suspect this is the issue.

So to be specific, if I use =IF(COUNTIFS((DATEONLY({RD BAL 001 (Large balance) Range 1})), @cell = [Daily Verification Due Date]@row) > 0, "Calibrated", "Not calibrated", where Range 1 is a specific date cell in the reference sheet, the formula calculates, but compares the entire date column in my current sheet to one date in the reference sheet.

I tried =IF(COUNTIFS((DATEONLY({RD BAL 001 (Large balance) Range 1})), @cell = [Daily Verification Due Date]@row) > 0, "Calibrated", "Not calibrated", where Range 1 is the whole date column in the reference sheet and this returns a "invalid data type". If I replace "@cell" with "@row" it returns "unparseable". If I remove the "@ cell" completely I get "incorrect argument".

Jeff Reisman

@Cpatt

Let me see if I can tell what you're trying to do.

IF the count of dates in the remote date range where the date equals the date in the daily verification Due Date cell on this row is greater than 0, set the cell to Calibrated, otherwise set it to Not Calibrated.

Correct?

I also encountered the invalid data type error.

In your source sheet, can you add a helper Date-type column to convert the automated Created date into the DATEONLY format? Just use

=DATEONLY(Created@row)

and make it a column formula. Then reference that helper column in your formula on the other sheet:

=IF(COUNTIF({RD BAL 001 (Large balance) Helper Date}, [Daily Verification Due Date]@row) > 0, "Calibrated", "Not calibrated")

(I tested this exact formula and it worked!)

Cpatt

Edit - I figured this part out - I did not have the column format set to date.

@Jeff Reisman - When I tried to create the helper column with the formula =DATEONLY(Created@row) it returns an "invalid column value"

Cpatt

@Jeff Reisman - Once I got the helper column figured out this worked!!! THANK YOU SO MUCH!!!! This has been driving me crazy for weeks!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/316688#Comment_316688

Excellent, happy it worked out.