Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Solved
Jump to Solution
3669 2
cancel
Showing results for 
Search instead for 
Did you mean: 
plyske
7 - App Architect
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
Mariusz_S
7 - App Architect
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},"🟢",""),"")

See Solution in Thread

8 Replies 8
Konrad_Schafers
6 - Interface Innovator
6 - Interface Innovator

You mean something like 

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

?

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.

Mariusz_S
7 - App Architect
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.},"🟢",""),"")
plyske
7 - App Architect
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.

Mariusz_S
7 - App Architect
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')

 

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

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

Mariusz_S
7 - App Architect
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},"🟢",""),"")
plyske
7 - App Architect
7 - App Architect

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