Hi @Jacek_Greber ,
Welcome to Airtable Community !
Can you please screenshot those fields so we can understand the relation between them? For example, where is the data for {next date} coming from?
Tip: When pasting a formula here, paste it in the Preformatted text as to make it easier to copy
Hey Mohamed,
thank you :winking_face:
Of course. Here are the screenshots:
The formula would look something like that
IF(
{routine type}='daily',
IF(WEEKDAY(DATEADD({task date}, 1, 'days'))=0,
DATEADD({task date}, 2, 'days'),
IF(WEEKDAY(DATEADD({task date}, 1, 'days'))=6,
DATEADD({task date}, 3, 'days'),
DATEADD({task date}, 1, 'days'))),
IF(
{routine type}='weekly',
IF(WEEKDAY(DATEADD({task date}, 7, 'days'))=0,
DATEADD({task date}, 8, 'days'),
IF(WEEKDAY(DATEADD({task date}, 7, 'days'))=6,
DATEADD({task date}, 9, 'days'),
DATEADD({task date}, 7, 'days'))),
IF(
{routine type}='bi-weekly',
IF(WEEKDAY(DATEADD({task date}, 14, 'days'))=0,
DATEADD({task date}, 15, 'days'),
IF(WEEKDAY(DATEADD({task date}, 14, 'days'))=6,
DATEADD({task date}, 16, 'days'),
DATEADD({task date}, 14, 'days'))),
IF(
{routine type}='monthly',
IF(WEEKDAY(DATEADD({task date}, 30, 'days'))=0,
DATEADD({task date}, 31, 'days'),
IF(WEEKDAY(DATEADD({task date}, 30, 'days'))=6,
DATEADD({task date}, 32, 'days'),
DATEADD({task date}, 30, 'days')))
)
)
)
)
Hope this helps
If it does, please mark it as Solution
The formula would look something like that
IF(
{routine type}='daily',
IF(WEEKDAY(DATEADD({task date}, 1, 'days'))=0,
DATEADD({task date}, 2, 'days'),
IF(WEEKDAY(DATEADD({task date}, 1, 'days'))=6,
DATEADD({task date}, 3, 'days'),
DATEADD({task date}, 1, 'days'))),
IF(
{routine type}='weekly',
IF(WEEKDAY(DATEADD({task date}, 7, 'days'))=0,
DATEADD({task date}, 8, 'days'),
IF(WEEKDAY(DATEADD({task date}, 7, 'days'))=6,
DATEADD({task date}, 9, 'days'),
DATEADD({task date}, 7, 'days'))),
IF(
{routine type}='bi-weekly',
IF(WEEKDAY(DATEADD({task date}, 14, 'days'))=0,
DATEADD({task date}, 15, 'days'),
IF(WEEKDAY(DATEADD({task date}, 14, 'days'))=6,
DATEADD({task date}, 16, 'days'),
DATEADD({task date}, 14, 'days'))),
IF(
{routine type}='monthly',
IF(WEEKDAY(DATEADD({task date}, 30, 'days'))=0,
DATEADD({task date}, 31, 'days'),
IF(WEEKDAY(DATEADD({task date}, 30, 'days'))=6,
DATEADD({task date}, 32, 'days'),
DATEADD({task date}, 30, 'days')))
)
)
)
)
Hope this helps
If it does, please mark it as Solution
You could simplify that a great deal with SWITCH()
IF(
AND({routine type}, {task date}),
DATEADD({task date}, SWITCH(
{routine type},
'daily', SWITCH(WEEKDAY(DATEADD({task date}, 1, 'days')), 0, 2, 6, 3, 1),
'weekly', SWITCH(WEEKDAY(DATEADD({task date}, 7, 'days')), 0, 8, 6, 9, 7),
'bi-weekly', SWITCH(WEEKDAY(DATEADD({task date}, 14, 'days')), 0, 15, 6, 16, 14),
'monthly', SWITCH(WEEKDAY(DATEADD({task date}, 30, 'days')), 0, 31, 6, 32, 30),
0
), 'days'))
I love you Guys! It works :winking_face: