Notion Answers

Help between Notion users


Register & Ask

It's free & easy

Get answers

Answers, votes & comments

Vote and select answers

Receive points, vote and give the solution

Question

1vote

Dates from recurring template not working in formula

I have a task database set-up and a formula to show if tasks are completed early, on time, or late. It's working fine for one-time tasks, but apparently not recognizing due dates that are set by recurring templates. Any ideas for how to fix this? Or procedures for asking to fix this glitch?

0vote

polle commented

¿Which recurring templates?
¿How that works?
¿Which formula?

Without seeing exactly how you have everything, it is really difficult to guess. Please share the complete information to have a look.

0vote

leslie1165 commented

I have a large number of repeating templates set up to automatically set a due date of "today" (which populates the Due Date field with the date on which it is created.) I use the following formula to populate a "Complete Status" field:

ifs(empty(prop("Due Date"))," ",prop("Date Completed")<prop("Due Date"),"Early",prop("Date Completed")==prop("Due Date"),"On Time",prop("Date Completed")>prop("Due Date"),"Late","**")

It works for tasks that I create individually, but any task created by a template yields "**" for any date completed. If I manually edit the Due Date (clear and reset), it works correctly.

After a bit of testing, I found that it correctly calculates early or late if I change the date completed. But doesn't show on time unless I manually edit the due date.

1 Answer

0vote

polle Points82410

I tested it and you are correct.

If you do it by the minute, live and without waiting any second, it does not work.

You need to wait for it to work.

The template creates one now at 5:03 PM today, you set Completed at 5:05 PM today (to test) = Will Not Work.

Let it work 24 hrs, if it is created today at 5:03 PM, for sure you will not add a completed date at 5:05 PM.

So I believe that is the problem, that you are testing it as a live environment, when it is not, it needs time to work. (24 hrs or more)

Created today by template (no time means 0:00), then after 24 hrs you select an end date with no time as well.

Otherwise you will need to include time in the dates. In the template, in the completed date and in the formula, that way the exact minute can work live as you are testing.

If you are not doing it 3 minutes later to test and you are actually waiting more than 24 hours, then this can be a bug. I will keep my test running till tomorrow so the item is created and add a day later the completed date to see if the same happens.

0vote

leslie1165 commented

My repeating templates have dates set to "today" (not "now") so I don't think a few minutes should make a difference. I've waited until the next day to add date completed, and I still get the same result. I've also added date completed on the same day (i.e.: 6/28/2024 due date and date complete, all entered on 6/28/2024) then waited until 6/29/2024 to see if the correct result appeared. So far that hasn't worked either.
I think it may be a bug, but I'll keep testing over the next few days.

Thanks for your help!

0vote

polle commented

I tested it and it is not working unless you select things manually as you said.

I still believe the problem is with time and seconds as I mentioned above, which are not considered in the formula and causes this to fail. Yes, a few minutes and even seconds make a difference.

What confuses is why are you creating items for due date as today when you will never be able to finish them before or on time, it will always be late.

The logic for due dates is that you have a due date in the future and you can finish it before, on time or late, but with you structure that will never happen.

2 extra things.

1- If you need it to match exactly, use timestamps.
Create 2 new formula properties.

  • DueTime and CompletedTime
  • Use timestamp(prop("Due Date")) and timestamp(prop("Completed Date"))

That will give you the timestamp for both dates one in each column.

Hide the columns.

Then modify your formula to use the timestamp properties instead of the dates.

This will work exactly as you need with no errors.

2- BTW, I already sent this to the Notion team to get more info and see if this is possible to be a bug.

When I get more info I will update you here.

0vote

polle commented

Just an update to let you know that it is a bug for "today" and formulas. So this will be fixed in upcoming Notion updates.

In the meantime you can use what I suggested, as that works perfectly fine.

Please consider selecting the answer to mark the question as solved.

Please log in or register to answer this question.

...

Welcome to Notion Answers, where you can ask questions and receive answers from other members of the community.

Please share to grow the Notion Community!

Connect