Skip to main content
Solved

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

  • February 17, 2023
  • 8 replies
  • 85 views
P
Forum|alt.badge.img+11

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?

Best answer by Mariusz_S

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

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


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

K
Forum|alt.badge.img+9

You mean something like 

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

?

P
Forum|alt.badge.img+11
  • plyske
  • Author
  • Inspiring
  • February 17, 2023

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.

M
Forum|alt.badge.img+13
  • Mariusz_S
  • Inspiring
  • February 17, 2023

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.},"🟢",""),"")
P
Forum|alt.badge.img+11
  • plyske
  • Author
  • Inspiring
  • February 17, 2023

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.},"🟢",""),"")

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.

1 Attachments
M
Forum|alt.badge.img+13
  • Mariusz_S
  • Inspiring
  • February 17, 2023

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.


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')

 

P
Forum|alt.badge.img+11
  • plyske
  • Author
  • Inspiring
  • February 17, 2023

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). 

1 Attachments
M
Forum|alt.badge.img+13
  • Mariusz_S
  • Inspiring
  • Answer
  • February 17, 2023

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

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


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},"🟢",""),"")
P
P
Forum|alt.badge.img+11
  • plyske
  • Author
  • Inspiring
  • February 17, 2023

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

M
Terms & ConditionsAccessibility statement