function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Adrian McDonaghAdrian McDonagh 

Want to calculate spend between set date and current date

Just need some help with creating a formula, I am not sure whether it is possible but I imagine it is.

On the account object. I want to calculate the total spend between a set date (controlled by another field) and the current date. So as I change this set date you can see how much a particular company has spent since then.

I know you will need the opportunity fields to relate to but could anyone create a formula which I can work from or just any advice in general would be appreciated.
Shalini RShalini R
Hi Adrian McDonagh,

Good morning.
Here i have mentioned to calculate the difference between two datetime fields with minutes.

Please use if you need similar calculation.
Formula:
Text( 
FLOOR(Value(Text(End__c-Start__c))*24*60) 
)
Adrian McDonaghAdrian McDonagh
Thanks for your help.

Only problem with this is that when I include the date fields into this formula then I am getting this error "Error: Formula result is data type (Text), incompatible with expected data type (Number)".

The name of the date field I am using is "ATS_go_live_date__c". Then I need to have some validation in place to ensure that it only adds up opportunities from within that timeframe.
Shalini RShalini R
Hi Adrian McDonagh,

You declared formula as number return type, but result come with text type, so the above error occur.
Could you explain clearly second point.
I will tell you if i know.
 
Shalini RShalini R
Hi Adrian McDonagh,

ATS_go_live_date__c only save the date value, if ATS_go_live_date__c date value within that time frame.
is it correct.
Adrian McDonaghAdrian McDonagh
I have resolved the first issue by selecting "Text" for the "Formula return type".

Between ATS_go_live_date__c and todays date is the time frame the formula needs to look at. It then needs to add up the calculated amount field on any opportunities associated to the account. But only ones within the time frame.

Does that make sense?