Dynamic 2 weeks calendar solution

[Jesper_Holmstro]

Hey guys

Im stuck with a problem with some formulas, I got some real good help from @Andrey_Kovalev in December but now I'm stuck, so please if someone could advise. Long story short, I have 10 records (Mon-Fri odd and even weeks)and I want the date to change dynamically, i.e Today Monday the 16th will turn into Monday 30th after midnight, so just a date change on that record. We got this to work for one week but when the date turns into Sunday 22nd the 1 row will go back to the 23rd, it doesn't remain to the 30th. The Formulas are:

Calendar formula

IF(Today <= Date, Date, DATEADD(Date, 14, "day"))

Date formula

DATEADD(Today,Autonumber-WEEKDAY(Today),"day")

Today formula

Today()

And some pics from the base so you understand:

Works fine for this week and date in the Calendar formula works:

And on Sundays it all jumps back:

Best regards

Jesper

[Micki_O_Neil]

Let me make sure I understand better what you're trying to achieve. You want:

• Records 1-5 to always be a date for *this* week up until that day has passed. Then it should be a date for two weeks ahead.
• Records 6-10 should always be dates for "next" week.

Is that right?

[Micki_O_Neil]

Wait, what I was interpreting is what it's doing, I think. So is what you want to "work your way down" until you complete the two weeks and then start over? If so, this is likely best achieved with an automation.

[Jesper_Holmstro]

Hi @Micki_O_Neil Yes sort of, Record 1-5 is always Uneven weeks, 6-10 is Even weeks. So when Today (yesterday 16th of january) is passed it will become January the 30th instead. Just a date change. So when Today (17th of January) has passed record 2 changes date to 31st. I have connected this via APIs so that an app always reads from the same line, so I can't have that record 1 suddenly is a Monday on an even weeks. Does it make any sense?

[Micki_O_Neil]

The challenge you're having is, in this case, you need two different formulas: one when "today" is during an even week and another is when "today" is during an odd week.  You should be able to come up with an If statement that is basically: If Today() = even week, then Formula 1, else Formula 2. 

You'll need a formula to determine if Today is in an even week or odd and the second formula, but I think that's the basic outline of what you need.  (I don't know what either of those formulas are off the top of my head, but I'll think about them today at work 🙂 ).

[Micki_O_Neil]

(You can use the formula Weeknum() to create the first part of the if statement.)

[Jesper_Holmstro]

Hey @Micki_O_Neil 

Ok that makes sense, but I really don't know how to write that one to make it work correctly

[Micki_O_Neil]

You actually need a combo of 4 different "ifs" in the Calendar Formula - based on whether "today" is an odd or even week and whether the record is in the 1st or 2nd week.

I think this should work:

You can use your "Odd Even" as my "First/Second" field, although my 0s and 1s are reversed, so start with "IF({Odd Even} = 0," rather than {First/Second}=1.

(Is there a reason an automation wouldn't have worked. That would have been a lot simpler.)

[Jesper_Holmstro]

Hey @Micki_O_Neil 

Thank you so much for your time on this, but I still get an error when trying to type in the formula, it says it causes a "Circular reference".

Have I written the formula wrong or is something missing?

IF({Odd even}=0,IF(MOD(WEEKNUM(Today),2)=1,IF(Today<=Date,Date,Dateadd(Date,14,"day")),DATEADD(Date,7,"day")),if(mod(weeknum(Today),2)=1,Date,If(Today<=Dateadd(date,-7,"day"),Dateadd(Date,-7,"day"),Dateadd(Date,7,"day"))))

[Micki_O_Neil]

My guess is it's your Odd Even field. If that's a formula referring back to Calendar, it would create a circular reference. I just had it is an integer field with 1 or 0, to identify whether I wanted it to be Odd or Even, not whether it was or not.