Update a Sheets Row When a Date Filter is Met

[statipletus]

Hello,

My goal is to watch Google Sheet and look into if the last order date was more than 7 days ago or less. If the last order was more than 7 days ago, the status should be “Inactive”. If it was 6 or less days ago - “Active”. To create a workflow, I mate a simple scenario with a router and 2 filters.

I’m getting an error all the time and my filter is not working. Below you’ll see pretty much all the workflow I have.

Important data:

My Sheet Date format look like this: 08-Mar-23
My filter function for more than 7 days look like this: {{parseDate(1.6; “DD/MMM/YY”) < addDays(now; -7)}}. Time operator is equal to “Less than”.
My filter function for less than 7 days look like this: {{parseDate(1.6; “DD/MMM/YY”) >= addDays(now; -6)}}. Time operator is equal to “Greater than or equal to”.

If you could help me solve this issue, I would be very grateful!

[samliew]

Welcome to the Make community!

Your parseDate function seems to be using a different date format compared to the value in Column G.

One is using hyphens, and the other is using slashes.

Also you want to move this bit into the second field

Then use a Date operator instead of a Time operator.

[statipletus]

@statipletus

My goal is to watch Google Sheet and look into if the last order date was more than 7 days ago or less. If the last order was more than 7 days ago, the status should be “Inactive”. If it was 6 or less days ago - “Active”. To create a workflow, I mate a simple scenario with a router and 2 filters.

Thank you very much! It worked. I’m a make.com / automation newbie, so please bear with me, cuz now I got another problem.

At first I used time operators and now I swithced to Datetime operators, right? What functions should I choose in each scenario. Now I’m using “Later than” for active scenario and “Earlier than or equal to” in not active scenario. However, it makes wrong decisions and doesn’t distinct active scenarios.

I try to change vice versa, but it didn’t help. Am I missing smth here?

Thank you in advance!

[samliew]

You are still using the wrong format.

Please take a look closely at https://www.make.com/en/help/functions/tokens-for-date-time-formatting and see what can be used for each of the day, month, and year.

Hint: the alphabets are case-sensitive, so yy is not the same as YY

[statipletus]

Yes, I see that. I read this, but just omitted the last part.

However, the question remains the same:

At first I used time operators and now I swithced to Datetime operators, right? What functions should I choose in each scenario. Now I’m using “Later than” for active scenario and “Earlier than or equal to” in not active scenario. However, it makes wrong decisions and doesn’t distinct active scenarios.

I try to change vice versa, but it didn’t help. Am I missing smth here?

Thank you!

[samliew]

You might not need Make, the sheets formula is

=IF(DAYS(NOW()-7, DATEVALUE(A2)) > 7, "INACTIVE", "ACTIVE")

[samliew]

Welcome to the Make community!

Yes, that is possible. You’ll need a minimum of two modules:

Here is the original sheet

1. Filter rows that need a status change

This saves you operations on rows that doesn’t need changing

As you can see, I am using the DATETIME Operators “Earlier than” and “Later than or equal to”

2. Update status cell value based on date

After running, status cell is populated correctly

Give it a go and let us know if you have any issues!

[statipletus]

Finally it worked! I’m so much grateful for you Sam!

Best wishes:)

[samliew]

No problem, glad I could help!

1. If you have a new question in the future, please start a new thread. This makes it easier for others with the same problem to search for the answers to specific questions, and you are more likely to receive help since newer questions are monitored closely.

2. The Make Community guidelines encourages users to try to mark helpful replies as solutions to help keep the Community organized.

This marks the topic as solved, so that:

others can save time when catching up with the latest activity here, and

• allows others to quickly jump to the solution if they come across the same problem

To do this, simply click the checkbox at the bottom of the post that answers your question:

3. Don’t forget to like and bookmark this topic so you can get back to it easily in future!