Date calculation help

[alexis]

I have the following formula:

= sum({$<[TransactionLinesDate] = {'>=$(=Date(vStartDate))<=$(=Date(vEndDate))'},

[TransactionLinesType] = {'I', 'C', 'T'}>}

TransactionLinesAmount)

vStartDate and vEndDate are typically the beginning of the month so we may have value 01/01/2015 (42005) and 01/02/2015 (42036).

I want to repeat the above calculation but for the month before - in other words instead of calculating for start of January until start of February, I want start of December 2014 to start of January 2015....

In other words I want vStartDate and vEndDate to be adjusted by one less month.

I have even tried the following and cannot get it to work...

:

= sum({$<[TransactionLinesDate] = {'>=$(=Date(vStartDate - 31))<=$(=Date(vEndDate - 31))'},

[TransactionLinesType] = {'I', 'C', 'T'}>}

TransactionLinesAmount)

Any help would be greatly appreciated...

[anbu1984]

= sum({$<[TransactionLinesDate] = {'>=$(=AddMonths(Date#(vStartDate,'DD/MM/YYYY'),-1))<=$(=AddMonths(Date#(vEndDate,'DD/MM/YYYY'),-1))'},

[TransactionLinesType] = {'I', 'C', 'T'}>}

TransactionLinesAmount)

[alexis · Accepted solution]

Thanks Anbu

I adapted what you suggested and got the desired result with the following - for some reason I was not getting the desired result with Date# and the formatting:

sum({$<[TransactionLinesDate] = {'>=$(=AddMonths(Date(vStartDate),-1))<=$(=AddMonths(Date(vEndDate),-1))'},

[TransactionLinesType] = {'I', 'C', 'T'}>}

TransactionLinesAmount)

Thanks

Alexis

[anbu1984]

If the values stored in vStartDate and vEndDate are date, then you can directly use Addmonths() without Date()

[alexis]

vStartDate and vEndDate are numeric (e.g. 42005 for 01/01/2015) hence why I "Date" them and then deduct 1 month as you suggested.

Thanks again

Alexis