Re: To create a formula that adds a green circle if a field contains the date of the next coming...

Solved
2746 4
cancel
Showing results forÂ
Did you mean:Â
7 - App Architect

Hello everyone!

So first off I have this formula field:

``````DATETIME_FORMAT(
IF(
DAY({TilfÃ¸jet d.}) <= 10,
DATETIME_PARSE(CONCATENATE(YEAR({TilfÃ¸jet d.}), '-', MONTH({TilfÃ¸jet d.}), '-11'), 'YYYY-MM-DD'),
DATEADD({TilfÃ¸jet d.}, IF(WEEKDAY({TilfÃ¸jet d.}) <= 3, 3 - WEEKDAY({TilfÃ¸jet d.}), 10 - WEEKDAY({TilfÃ¸jet d.}) + 3), 'day')
),
'DD/MM/YYYY'
)``````

The name of that field is 'Kan laves d.' and shows me the date of the next coming wednesay based off of the date the record was created. So for example if the Record was created today, it will show me 25/2/23.

So what I want to make now is a new formula field, that will add a green circle for all the records that can be made the next coming wednesay, and only for those.

Can someone help me with this?

1 Solution

Accepted Solutions
7 - App Architect

Ah there was a mistake in the date format.
"Kan laves d." :

``DATETIME_FORMAT(DATEADD({TilfÃ¸jet d.}, IF(WEEKDAY({TilfÃ¸jet d.})<=3, 3-WEEKDAY({TilfÃ¸jet d.}), 10-WEEKDAY({TilfÃ¸jet d.})), 'days'), 'DD/MM/YYYY')``

ðŸŸ¢ formula field:

``IF({Following WED Date},IF(DATETIME_FORMAT(DATEADD(TODAY(), IF(WEEKDAY(TODAY())<=3, 3-WEEKDAY(TODAY()), 10-WEEKDAY(TODAY())), 'days'), 'DD/MM/YYYY')={Following WED Date},"ðŸŸ¢",""),"")``
8 Replies 8
6 - Interface Innovator

You mean something like

IF ( [Kan laves d.] = [calculation of next wednesday], ðŸŸ¢, "")

?

7 - App Architect

Exactly!

I tried with this one:

``````IF(
AND(
IS_AFTER({Kan laves d.}, TODAY()),
WEEKDAY({Kan laves d.}) = 3,
WEEKDAY(TODAY()) <> 3
),
"ðŸŸ¢",
BLANK()
)``````

But Airtable won't accept that for some reason.

7 - App Architect

I think this should do the trick:

``IF({Kan laves d.},IF(DATETIME_FORMAT(DATEADD(TODAY(), IF(WEEKDAY(TODAY())<=3, 3-WEEKDAY(TODAY()), 10-WEEKDAY(TODAY())), 'days'), 'DD/MM/YYYY')={Kan laves d.},"ðŸŸ¢",""),"")``
7 - App Architect

Hi Mariusz_S

So this time it accepted the formula - nice!

However no input appears in the field afterward.

Please se attached picture (the blank field all the way to the right is the formula field with your formula.

BUT I actually just realised that something is wrong with the first formula too since the 25/2/23 isn't the next coming wednesday, but a saturday.. Hm. Can you see where the error is?

``````DATETIME_FORMAT(
IF(
DAY({TilfÃ¸jet d.}) <= 10,
DATETIME_PARSE(CONCATENATE(YEAR({TilfÃ¸jet d.}), '-', MONTH({TilfÃ¸jet d.}), '-11'), 'YYYY-MM-DD'),
DATEADD({TilfÃ¸jet d.}, IF(WEEKDAY({TilfÃ¸jet d.}) <= 3, 3 - WEEKDAY({TilfÃ¸jet d.}), 10 - WEEKDAY({TilfÃ¸jet d.}) + 3), 'day')
),
'DD/MM/YYYY'
)``````

A little side note: The formula is made (or at least that's the plan) to nok make use of the first 10 days of the month since we won't make payments in that period, only after the first 10 days.

7 - App Architect

I think your original formula (stored on "Kan laves d.") might have an error. Can you try the one below and report back?

``DATETIME_FORMAT(DATEADD({TilfÃ¸jet d.}, IF(WEEKDAY({TilfÃ¸jet d.})<=3, 3-WEEKDAY({TilfÃ¸jet d.}), 10-WEEKDAY({TilfÃ¸jet d.})), 'days'), 'YYYY-MM-DD')``

7 - App Architect

That formula worked like a charm. Now it shows 22/2/23!

However the other formula still shows a blank field (see image).

7 - App Architect

Ah there was a mistake in the date format.
"Kan laves d." :

``DATETIME_FORMAT(DATEADD({TilfÃ¸jet d.}, IF(WEEKDAY({TilfÃ¸jet d.})<=3, 3-WEEKDAY({TilfÃ¸jet d.}), 10-WEEKDAY({TilfÃ¸jet d.})), 'days'), 'DD/MM/YYYY')``

ðŸŸ¢ formula field:

``IF({Following WED Date},IF(DATETIME_FORMAT(DATEADD(TODAY(), IF(WEEKDAY(TODAY())<=3, 3-WEEKDAY(TODAY()), 10-WEEKDAY(TODAY())), 'days'), 'DD/MM/YYYY')={Following WED Date},"ðŸŸ¢",""),"")``
7 - App Architect

Mariusz! Thanks a lot. I am very grateful for your help with this. Thanks!