Skip to main content
Solved

Move task on the monday when it's saturday or sunday?

  • August 29, 2022
  • 5 replies
  • 56 views
J

Hey Guys,

first time here. Airtable support forwarded me here. I need your help.

I have a such formula:

IF(
{routine type}=‘daily’,
DATEADD({task date}, 1, ‘days’),
IF(
{routine type}=‘weekly’,
DATEADD({task date}, 7, ‘days’),
IF(
{routine type}=‘bi-weekly’,
DATEADD({task date}, 14, ‘days’),
IF(
{routine type}=‘monthly’,
DATEADD({task date}, 30, ‘days’)
)
)
)
)

and I would like to combine it with something like this:

IF(
WEEKDAY({next date})=0,
DATEADD({next date}, 1, ‘days’),
IF(
WEEKDAY({next date})=6,
DATEADD({next date}, 2, ‘days’),
{next date}
)
)

Can you help me, please? The first formula creates new task depending on the routine type. Weekly routine, monthly routine, etc. The second formula should move the task to the Monday if the first formula lands task on the Saturday or Sunday.

Appreciate your help.

Jack

Best answer by Kamille_Parks11

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

5 replies

Mohamed_Swella1
Forum|alt.badge.img+17

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

J
  • Jacek_Greber
  • Author
  • New Participant
  • August 29, 2022

Hey Mohamed,

thank you :winking_face:

Of course. Here are the screenshots:

Mohamed_Swella1
Forum|alt.badge.img+17

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 :slightly_smiling_face: If it does, please mark it as Solution

Kamille_Parks11
Forum|alt.badge.img+27

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 :slightly_smiling_face: 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'))
J
  • Jacek_Greber
  • Author
  • New Participant
  • August 30, 2022

I love you Guys! It works :winking_face:

Terms & ConditionsAccessibility statement