General caster - formula for specific weekday

Hello Community and GC-Team,

I am in need of a certain formula. We have different release dates for our products in a week and they are produced a day before. There is a product that officially releases every sunday but is produced thursday. Otherwise its produced a day before. Can I make a formula that,

if release date is sunday, display date of thursday
or more like
if release date is sunday, - 3 (so its thursday)?

Edit: Right now, I have come up with this:
IF({ET} = WEEKDAY(7), FORMAT_DATE(SUBTRACT_DAYS({ET}, 1), FORMAT_DATE(SUBTRACT_DAYS({ET}, 3))))
(ET is the release date column)
But it sets any date - 3 weekdays, not only when it’s sunday.
This is probably because monday doesnt understand Weekday or Weekday (7).

I hope you understand what I mean.
Since I need automations to work with this, I need General Caster for it, right?

I’d be very glad if you can help me out here.

I managed to get it to work, but not quite. I need help from the GENERAL CASTER team.

I made a formula column (it is named “Sonntag”) that says:
FORMAT_DATE({ET}, "dddd")
ET is the release date

Then I created another formula column:
IF({Sonntag} = "Sunday", FORMAT_DATE(SUBTRACT_DAYS({ET}, 3)), FORMAT_DATE(SUBTRACT_DAYS({ET}, 1)))

which subtracts 3 days if “Sonntag” says “Sunday” and subtracts 1 if “Sonntag” says something else (Friday for example)

Now I used the General Caster App to perform this last formular and cast it to an actual column, which I can use in my automations. I used the Date column (because this is the production day).
I have one item where it works. For example the release date {ET} is the 6th February, my {Sonntag}-formula casts “Saturday”, my other formula casts “Feb. 05, 2021” - which is correct! Now the General cast formula "When any column changes, perform “Formula” and cast result to “Production date” projects the Feb. 05, 2021 as well. It does work in this case.
I tried it with the 7th February, which is a sunday, and my last formula casts “4th Febraury” - which is also correct. Now the General Caster casts “Feb. 06, 2021”.
Why?

Btw. what column needs to change so that the general caster activates. Any? So I can also use any other column, like status?

Hi @MNie!

I believe @rob from the General Caster team may be the better candidate to answer this question, but I’m happy to take a look for you as well.

It seems like the issue here is that the General Caster isn’t casting the correctly calculated date (i.e. Feb 6 instead of Feb 4), is that right?

Regarding your last question about which column to change, this would depend on the recipe you’re using! For instance, if you’re using the “When any column changes…” recipe, then indeed when any column on this board is altered, the formula will be performed and then cast.

-Helen

Hi @Helen and maybe at one point @rob,

This I actually got to bypass somehow, so this problem is solved now.

I do have another Problem.

Forget what I said before, I have new formulas etc.

I will need to explain it again:

We are a news company, publishing different news papers every day.
Product 1 is published every day, produced the day before.
Product 2 is published every sunday and wednesday, produced thursday and monday.

Now in my board I have the following (for this topic relevant) columns:
{Product} - A dropdown. “Product 1” and “Product 2” are the options.
{Release} - Date column for the release date.
{Production} - Date column for the production date.

Now I have different Formula columns:
{Weekday} - FORMAT_DATE({Release}, "dddd")
{Text} - a text column used for a GC-Integration to bypass the problem in my comment before.

{Formula 1} -
SWITCH({Text}, "Wednesday", FORMAT_DATE(SUBTRACT_DAYS({ET}, 2)), "Sunday", FORMAT_DATE(SUBTRACT_DAYS({ET}, 3)), "Monday", FORMAT_DATE(SUBTRACT_DAYS({ET}, 1)), "Tuesday", FORMAT_DATE(SUBTRACT_DAYS({ET}, 1)), "Thursday", FORMAT_DATE(SUBTRACT_DAYS({ET}, 1)), "Friday", FORMAT_DATE(SUBTRACT_DAYS({ET}, 1)), "Saturday", FORMAT_DATE(SUBTRACT_DAYS({ET}, 1)))

{Formula 2} -
SWITCH({Product}, "Product 2", {Formula 1}, "Product 1", FORMAT_DATE(SUBTRACT_DAYS({Release}, 1)))

Now my General Caster Integrations:
"When any column changes, perform “Weekday” and cast result to “Text”,
"When any column changes, perform “Formula 2” and cast result to “Production”.

The first integraton works fine.
The second one does not.
It casts the dates of Formula 2 for “Product 1” but not for “Product 2”, it is an invalid date.
I also tried casting it into a new text column, but it only casts “Product 1” and for “Product 2” nothing at all, not even “invalid date”.

Here I tried to recreate the problem for you to see and GC decided to cast one correctly, but the other one is still “invalid date”.

Why cant General Caster just cast what is in the Formula 2 column? @rob

I found a comment by @rob linking to how a date must be formatted, that helped… kinda but the issue is that General Caster cant retrieve information out of a formula if in this formula another formula is used. So everytime I use a formula, I need to use GC to cast its result to a text column, then use another formula using the new text column, then use GC again. On my board are 3 GC-Integrations active now but thats ok. I can hide all “useless” columns for my coworkers so I guess that works. Still not perfect tho.

Any chance GC will get updated for this?

Also, whenever I want to use GC for anything timeline-related, it just doesnt comply. Sometimes its “invalid date, invalid date” (even though I only have one Date in my formula) or it just doesnt do anything at all and leaves it blank.

Currently we have no plans for adding support to nested formulas in GC.

Any tips for using GC with timelines (start date) @rob?

@MNie
Get inspiration here:

@rob
I dont get any inspiration from this. I also tried the exact formula from this comment (even though I dont need it) but that doesnt even work for me. General Caster just doesnt work with Timelines. Needs a fix at least.