Add date output wrong

[Sime_Jurlina]

Hi. I am trying to create a contract expiry reminder. I have the Google sheet with the expiry dates (and other info) plus the Date&Time and Adddate nodes. So I define the today’s date, add 30 days and tell the agent to send me all rows that are expiring 30 days from now. I have a row with matching test date. Instead in the output I only get a row that is 3 days from today. How to fix this?

[Gallo_AIA]

Hello!

Ensure consistent date formats, when retrieving dates from Google Sheets, they might be in different formats or include time components.

1. You could sse the date & time node in n8n to format both the expiry dates from Google Sheets and the calculated date (today + 30 days) to a consistent format, such as YYYY-MM-DD.

2. Use the filter node for comparison: after formatting, use the filter node to compare the dates. Set the condition to check if the formatted expiry date is equal to the calculated date. Ensure also that both sides of the comparison are strings in the same format.

Note: maybe (idk) timezone differences between n8n and Google Sheets can cause mismatches.

Cheers

[Sime_Jurlina]

Thanks. That was all explained in the prompt and node Date&Time and AddDate were used. Still getting garbage out.

[Gallo_AIA]

Hello @Sime_Jurlina,
If you agree, I would divide the problem into small steps to understand what is not working.

I think it is the agent’s job because he is the only one who can misinterpret the indications, the rest are “mechanical” nodes that only add information.

It also occurred to me that the agent may not know what day it is so you could add a {{$now}} in the prompt to help them.

If you share more information about the individual steps, we can reason together and understand where the problem lies.

[Sime_Jurlina]

So the first discovery was that the AI agent had the wrong notion of what today’s date is. This is solved in the Date&Time node. Also there was a problem comparing expiry date to the today’s that. I thought that I solved that in the AddDate node by adding 30 days to the today’s date. But I am still getting the wrong output although I put test rows with the exact date 30 days from now.

[Gallo_AIA]

probably the problem lies in the calculation of the +30 days that the node or agent misinterprets.
Can we try for a shorter period to figure out what’s going on?

If we spend a +1 day, does the same mistake happen?

[Sime_Jurlina]

Yes, it does.

[Sime_Jurlina]

I keep getting different results with the same prompt. Here is the prompt, what am I doing wrong?

1. Role:
   You’re an AI agent for contract management, responsible for reminding me about contracts expiring soon.

2. Task:
   Your main job is to check your Google Sheet. Find all contracts where the “Duration end” date is exactly 1 day from today. Once found, send a list of these contracts to “MY EMAIL”.

Each email should include these columns: “Duration start”, “Duration end”, “Vendor name”, and “Service description”.

3. Input:
   You will access my Google Sheet called “Support contracts”, specifically the “List” sheet, “Table1”. It has these columns: “Category”, “Contract type”, “Duration start”, “Duration end”, “Payment terms”, “Vendor name”, “Service description”.

Heads up: Dates in the “Duration end” column are in dd.mm.yyyy format.

4. Tools/Actions:
   You have these tools:

Use the Open AI chat model.
Read dates from the “Duration end” column in the Google Sheet.
Use the n8n “Date & Time” node to:
Get today’s current date (in ISO format).
Convert the dd.mm.yyyy dates from your Google Sheet into a comparable format (like ISO 8601).
Format dates back to dd.mm.yyyy for the emails.
Use the n8n “AddtoDate” node to:
Calculate the exact date that is 1 day from today. You’ll do this by adding 1 day to the result you got from the “Date & Time” node for today’s date.
Perform precise date comparisons: Only include rows where the contract’s “Duration end” date is exactly equal to the date you calculated for “1 day from today”.
Send emails to “MY EMAIL”.
If a cell in “Duration end” is empty, just skip that row.

5. Output:
   Your emails should look like this:

Subject Line: “Contract expiry reminder - 1 day”
Email Body:
Start with: “This is an automated reminder regarding your contract expiries.”
Then, include an HTML-formatted table with “Duration start”, “Duration end”, “Vendor name”, and “Service description” for the expiring contracts. Make sure “Duration start” and “Duration end” are in dd.mm.yyyy format.
End with: “Wish you a nice day.”