Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 17, 2023 05:35 AM
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?
Solved! Go to Solution.
Feb 17, 2023 07:08 AM
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},"🟢",""),"")
Feb 17, 2023 06:29 AM
You mean something like
IF ( [Kan laves d.] = [calculation of next wednesday], 🟢, "")
?
Feb 17, 2023 06:37 AM
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.
Feb 17, 2023 06:39 AM
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.},"🟢",""),"")
Feb 17, 2023 06:54 AM - edited Feb 17, 2023 06:58 AM
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.
Feb 17, 2023 06:57 AM - edited Feb 17, 2023 07:00 AM
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')
Feb 17, 2023 07:02 AM
Feb 17, 2023 07:08 AM
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},"🟢",""),"")
Feb 17, 2023 07:56 AM
Mariusz! Thanks a lot. I am very grateful for your help with this. Thanks!