How to create formula for date > 30 days, based on certain column values?

For eg. I have to create reports to show activities that were assigned to someone, and that someone has not yet completed?

Column 1: Finance ASSIGN Date = Date the activity was assigned to Finance Analyst

Column 2: Finance RECEIPT Date = Date that Finance Analysts receives the assignment

I only need to Flag Column 2 (Finance RECEIPT Date) when the Receipt Column is BLANK and not consider "N/A" values.

does that make sense?

q.PNG

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @Not so formula savvy

Try this. Although your title and screenshot indicated >30d, your example didn't mention it. If >30d isn't needed, delete the highlighted segment from the formula.

=IF(AND(ISDATE([Finance ASSIGN Date]@row), TODAY() >= [Finance ASSIGN Date]@row + 30, [Local Finance Receipt Status]@row = "", [Finance RECEIPT Date]@row = ""), "Late")

Will this work for you?
Kelly

Not so formula savvy

Hi Kelly,

I do need the > 30 days factored in…so I plugged in the formula and it came back with an #Invalid operation??

Kelly Moore

Hey @Not so formula savvy

Please check your Date columns and make sure the column type = Date. Do NOT check the box for Date only since you are also writing in the cells.

Not so formula savvy

it still isn't working. I unchecked the dial that restricts the column to date only and it still didn't work. Those 2 columns (Finance ASSIGN Date and Finance RECEIPT Date) are part of an automation I have set up, so it auto populates from the automation itself. Could that be why the formula isn't working in the "FA>30" column?…

Kelly Moore

@Not so formula savvy

No. If the dates are actual dates then it should work. It did work in my test sheet. Is it possible for you to take a screenshot of the actual formula (with the colored text), as well as copy your formula back to me. This will help me troubleshoot

Not so formula savvy

did you mean these?

q1.PNG

q2.PNG

q3.PNG

Kelly Moore

Hey @Not so formula savvy

I was able to replicate your error. Try this:

=IF(ISTEXT([Finance ASSIGN Date]@row), "", IF(AND(ISDATE([Finance ASSIGN Date]@row), TODAY() >= [Finance ASSIGN Date]@row + 30, [Local Finance Receipt Status]@row = "", [Finance RECEIPT Date]@row = ""), "Late"))

Will this work for you?
Kelly

Not so formula savvy

You are genius!!…this worked….!!!! thank you so much!!!!! :)

dojones

Here is another option. If you are using a report, there is a filter option that states "Is In the Last". You can specify any number of days you want in the filter. I also spent time creating methods to get the last 30 days until I found that it is already there in the filter.

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.