Multi Field Formula to Count Days between Dates Across Multiple Columns

[ColinMarc]

A through E is what I currently have. I am trying to calculate the number of days between each one of the columns and get the output that is in columns E through J. So for column D2 the calculation to get I2 is the difference between the date and today's date. To get F5 it would be the difference between A5 and E5. Since there are two dates in that row it would be the difference between those two dates. 

Basically I am looking to calculate the number of days between each stage but since some stages don't have dates I'm having trouble figuring out what the formula would be to calculate it.

Appreciate any help!!

[atcodedog05]

Hi @ColinMarc 

You can try something like below.

DateTimeDiff(DateTimeToday(),DateTimeParse([Stage 1],"%m/%d/%Y"),"days")

Output:

Workflow:

Similarly for other days

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[ColinMarc]

This is very close to what I am looking for but for the one in Stage 1 (Row 5) with the date 10/7/2020 I want the # of days to be the # of days between stage 1 and stage 5. So instead of the difference between the present day and date in Stage 1 it would be the # of days between stage 5 and stage 1.

Hopefully this makes sense thank you!

[atcodedog05]

Hi @ColinMarc 

Here is the workflow with updated calculation.

[ColinMarc]

Once again this is on the right track, but the solution won't work for every scenario. For example if the date in Stage 1 is 10/2/2020 and there is a date in Stage 2 of 10/10/2020 and then a date in Stage 5 of 10/19/2020. I would want it to calculate the #of days between stage 1 and 2 and then also # of days between stage 2 and 5. If this isn't clear let me know!

[atcodedog05]

Hi @ColinMarc 

To get days difference you can use below formula

DateTimeDiff(
DateTimeParse([Stage 5],"%m/%d/%Y"), // replace this
DateTimeParse([Stage 1],"%m/%d/%Y") // replace this
,"days")

 you can replace [Stage 5] and [Stage 1] the above formula is 

A-B <=> [Stage 5]-[Stage 1]

Accordingly you can build your calculations.

If you can give a expected output we i can build accordingly.

Your logic is still bit fuzzy. Expected output might help.

[ColinMarc]

Here is some more of the expected output, hopefully this makes it clearer now. But I need the formula to be somewhat dynamic. So if there is a date in Stage 1 and 3 it will calculate the # of days between. But if there isn't a date in stage 3 but there is a date for stage 4 it would calculate the # of days between stage 1 and 4.

[atcodedog05 · Accepted solution]

Hi @ColinMarc 

Here is a updated workflow for the task. It should as per your requirement.

Check with your new data.

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[ColinMarc]

This is awesome thank you so much!

[atcodedog05]

Happy to help 🙂 @ColinMarc 

Cheers and Happy Analyzing 😀