Skip to main content

Need help with a formula for choosing between 2 options

  • December 19, 2022
  • 8 replies
  • 65 views
P
Forum|alt.badge.img+11

Hello everyone! 

I've made a table that works as a 'Recurring tasks' table. When someone finishes a job, they'll mark the job as done by using a checkmark (se picture). 

The task can either be a daily task or a weekly task. If it's daily the 'Daglig' checkmark will be used, if it's weekly, the 'Ugentlig' checkmark will be used. 

Depending on what's chosen the table will find the right "next due date". 

The thing is however: the table is grouped by the field 'Hyppighed' (how often a task is being made), so when a task is being checked, it will be moved to an 'Empty' group since 'Hyppighed' isn't automatically filled. 

 

My question is: What should a formula look like in order for it to automatically fill in either 'daily' or 'Weekly' depending on what kind of task it is? 

I want this in order for the tasks to move to the right group again. 

Hope it makes sense. 

 

1 Attachments

8 replies

P
Forum|alt.badge.img+11
  • plyske
  • Author
  • Inspiring
  • December 19, 2022

 

A
  • Anonymous
  • December 19, 2022

I would do something along these lines.
IF(Daglig=1,"Daglig",
IF(Ugentlig=1,"Ugentlig",""))

P
Forum|alt.badge.img+11
  • plyske
  • Author
  • Inspiring
  • December 19, 2022

Hi,  

Thanks a lot for your reply! 

I just tried, and I do see that for example 'Daglig' is added, but the Record still shows up on in 'Empty', so it adds it before the Record is re-made.

 

 

A
  • Anonymous
  • December 19, 2022

I think I misunderstood your question and workflow, so ignore my formula.

You want the due date to be calculated, based on wether it is weekly or daily. Correct?

Also, why separate the completed check mark into daily and weekly? 

P
Forum|alt.badge.img+11
  • plyske
  • Author
  • Inspiring
  • December 19, 2022

That's absolutely correct, yes! 

So when it calculates the due date, it moves the "old" Record to a "Done" view and re-makes the Record with the new due date. 

Regarding your second question; I think I ended up doing it that way because it's easier for my colleagues to choose. They don't like drop downs for some reason. They want it as easy as possible. 

A
  • Anonymous
  • December 19, 2022

That's absolutely correct, yes! 

So when it calculates the due date, it moves the "old" Record to a "Done" view and re-makes the Record with the new due date. 

Regarding your second question; I think I ended up doing it that way because it's easier for my colleagues to choose. They don't like drop downs for some reason. They want it as easy as possible. 


I hope I get your workflow now. 

This workflow assumes that a) the task starts the day it is created, and b) there are only daily and weekly tasks. Your team will only mark weekly tasks. Otherwise, it is a daily task.

I would do the following changes:

1. Make a new field call it Created On. Column type is Created Time

2. Convert Deadline to a formula

3. paste the below formula into Deadline

IF({Ugentlig}=1,

DATESTR(DATEADD({Created On},7,'days')),
DATESTR(DATEADD({Created On},1,'days')))

4. convert Hyppighed to a formula and paste this formula
IF(IF({Ugentlig}=1,"Ugentlig","Daglig")

5. delete the Daglig column

Makes sense? I'm assuming you have more daily tasks than weekly tasks. 
But what happens when the task is complete? Or is that not something you need to worry about. 

P
Forum|alt.badge.img+11
  • plyske
  • Author
  • Inspiring
  • December 20, 2022

I hope I get your workflow now. 

This workflow assumes that a) the task starts the day it is created, and b) there are only daily and weekly tasks. Your team will only mark weekly tasks. Otherwise, it is a daily task.

I would do the following changes:

1. Make a new field call it Created On. Column type is Created Time

2. Convert Deadline to a formula

3. paste the below formula into Deadline

IF({Ugentlig}=1,

DATESTR(DATEADD({Created On},7,'days')),
DATESTR(DATEADD({Created On},1,'days')))

4. convert Hyppighed to a formula and paste this formula
IF(IF({Ugentlig}=1,"Ugentlig","Daglig")

5. delete the Daglig column

Makes sense? I'm assuming you have more daily tasks than weekly tasks. 
But what happens when the task is complete? Or is that not something you need to worry about. 


Hello! 

When inserting the first formula (step 3), I get this: 

"Sorry, there was a problem saving this field. Invalid formula. Please check your formula text."

Can you help me figure out what the issue is?

A
  • Anonymous
  • December 20, 2022

It looks good to me.
Try to erase everything in the curly brackets { } and type the columns back in.

It could be a spelling mistake or something.

 

IF({}=1,

DATESTR(DATEADD({},7,'days')),
DATESTR(DATEADD({},1,'days')))
Terms & ConditionsAccessibility statement