I was wondering what is the best way to achieve this usecase - calculating the difference between two dates stored in a table, and storing the outcome in another field within the same table.
- From: 01/01/2001
- To Date: 04/04/2001
- number of days in between (a number field type)
You can do this in a single calculation:
(End date - Start date) / 1 days
In this calculation you first subtract the start from the end date, which gives you a Duration of the number of days.
To then get a number, you simply divide the result by a Specific value of type Duration, and give it the value of a single day.
After you have this calculation, you can store the result in a number field.
Hope this helps!
@Jesse thanks for the quick response. I have tried your suggestion and it worked.
The only thing I want to check is if I need some sort of “looping action” in this use case. I ask because i have a table that stores up to 12 lines items of the different dates (End date and start date).
By executing your suggestion without the looping, would the flow part be smart enough to run through the list of (end and start date), calculating the number of days in between them?
Good to hear that the solution works. At least partly
As for the 12 items, it depends on your case. If all 12 have the same dates, you can simply use a lookup to find all items you want to edit and use a single edit action.
However, I presume each item has different dates and thus a different number of days as a result. In that case you’d need to use a repeat action indeed and perform the calculation and then the edit action inside the repeat.
Usually, I try to minimize such situations by immediately performing the calculation when the item is created. I would make a flow part that handles exactly this calculation and updates the item, and place it anywhere that I create or update the item, right after the flow part that creates/updates the item.
Hope this helps!