workflow to create a customized calculate column and recalculate every day for sharepoint online list

Question

I have a sharepoint online list needs a calculate column (decimal number) based on other 4 columns' value in the same list:

1st column - registered date (date)

2nd column - require close date (date)

3rd column - actural close date (date)

4th column - base score (whole number )

calculation logic

1. if actual close date is earlier than require close date , value in the calculate column equal to the base score in the 4th column.

2. if acutual close date is later than require close date , the result column = (actual close date - require close date)/7 * 0.2 * base score.

3. if the actual close date column is empty, which means the item is still open: the current date of the computer will be used to go through the above two condition's caculation as "actual close date", and generate a value to the calculate column.

Question:

1. how can this be achieved by workflow designer, and give a value to the calculate column whenenver a new item is created , or an exist item is updated.

2. how can this workflow recurrent everyday automatically for all items (or items where the 3rd column is empty) in the background and update the value in the calcuated column automatically everyday ?

thank you

Answer 1

Instead of workflow I would try to use calculated column.It is sometimes tricky to get the formula right but calculated column syntax is almost the same as Excel. Try to make the calculation formula you need in excel and then use it to create calculated column in you SPOnline site.