Multi Row Formula - Subtract Date/Days Row 1 from Row 2, and so on.

[wonka1234]

Hi,

I am trying to use multi row formula with this logic: Subtract Date/Days Row 1 from Row 2, and so on.

I am getting an error here formula invalid type in subtraction operator.

[phottovy]

Hi @wonka1234 ,

There are two things you need to consider to get this to work. First, is your date field formatted as a Date type? If not, it will treat the field as a string and won't know how to subtract. Second, you can use the DateTimeDiff() function to find the difference between two dates:

DateTimeDiff([PMT Date], [Row-1:PMT Date], 'days')

I have attached a workflow that both converts the field to a date format and then uses a multi-row tool with the DateTimeDiff function.

[binu_acs · Accepted solution]

@wonka1234 Another way of doing this

[wonka1234]

Thanks for this! 

Do you know how to reverse getting null in first row? would like the null to be in last row.

[binu_acs]

@wonka1234 you need to adjust the formula in the Multi_Row tool

DateTimeDiff(DateTimeParse([Row+1:PMT Date String],'%m/%d/%Y'),DateTimeParse([PMT Date String],'%m/%d/%Y'),'days')

[phottovy]

As @binu_acs pointed out, you just need to modify the formula slightly to use row +1 first: 

DateTimeDiff([Row+1:PMT Date], [PMT Date], 'days')

[binu_acs]

@wonka1234 Thanks for accepting my solution,  you can also mark multiple solutions as accepted solution if they can solve your use case. I can see  @phottovy solution is just another way of doing the same.