function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Travis Lee 1Travis Lee 1 

Calculate number of days between dates for revenue purposes

Let me first start by saying I understand how to calculate the number of days between two dates. The bigger issue I need help with (that wouldn't fit in the title) is how to correctly allocate the days between those two dates to the correct periods. I found an old thread that was never solved that gave the perfect example:

Here's a good example that should clarify what I need:
Start_Date__c 6/29/11, End_Date__c is 10/20/1. I need to know: there are 2 Days Revenue in Q2.  92 Days Revenue for Q3, and 20 Revenue Days in Q4.

It's a formula to calculate the 2 days, 92 days, 20 days that I truly need to figure out in order to properly allocate daily spend for an advertising company. Most of what needs to happen exists in visual workflow currently, this formula would get plugged in to a bulkified visual workflow that assigns currency values to revenue schedule installments.

Any help would be appreciated!
Best Answer chosen by Travis Lee 1
LBKLBK
Hi Travis,
Very interesting use case.
Let me know if this helps.

1. You will need 4 different formula fields Q1 Revenue, Q2 Revenue, Q3 Revenue and Q4 Revenue.
2. And, a number field to store the fiscal year for which the calculation is done (without which the data will change as the days go by). I have added a custom field that stores a 4 digit year (Fiscal_Year__c).

And the formula for Q1 Revenue Days is
  
IF(
AND(Start_Date__c <= Date(Fiscal_Year__c, 3, 31), Start_Date__c >= Date(Fiscal_Year__c, 1, 1),
End_Date__c <= Date(Fiscal_Year__c, 3, 31), End_Date__c >= Date(Fiscal_Year__c, 1, 1)), 
End_Date__c - Start_Date__c,
IF(
AND(Start_Date__c <= Date(Fiscal_Year__c, 3, 31), Start_Date__c >= Date(Fiscal_Year__c, 1, 1),
End_Date__c > Date(Fiscal_Year__c, 3, 31)), 
Date(Fiscal_Year__c, 3, 31) - Start_Date__c, 
IF(
AND(Start_Date__c < Date(Fiscal_Year__c, 1, 1),
End_Date__c <= Date(Fiscal_Year__c, 3, 31), End_Date__c >= Date(Fiscal_Year__c, 1, 1)), 
End_Date__c - Date(Fiscal_Year__c, 1, 1), 
IF(
AND(Start_Date__c < Date(Fiscal_Year__c, 1, 1),
End_Date__c > Date(Fiscal_Year__c, 3, 31)), 
Date(Fiscal_Year__c, 3, 31) - Date(Fiscal_Year__c, 1, 1), 
0))))

You just have to replace the quarter start date and quarter end date to make it work for other quarters.

For example,
replace Date(Fiscal_Year__c, 1, 1) with Date(Fiscal_Year__c, 4, 1) and Date(Fiscal_Year__c, 3, 31) with Date(Fiscal_Year__c, 6, 30) for Q2 and so on.

Keep me posted.

All Answers

LBKLBK
Hi Travis,
Very interesting use case.
Let me know if this helps.

1. You will need 4 different formula fields Q1 Revenue, Q2 Revenue, Q3 Revenue and Q4 Revenue.
2. And, a number field to store the fiscal year for which the calculation is done (without which the data will change as the days go by). I have added a custom field that stores a 4 digit year (Fiscal_Year__c).

And the formula for Q1 Revenue Days is
  
IF(
AND(Start_Date__c <= Date(Fiscal_Year__c, 3, 31), Start_Date__c >= Date(Fiscal_Year__c, 1, 1),
End_Date__c <= Date(Fiscal_Year__c, 3, 31), End_Date__c >= Date(Fiscal_Year__c, 1, 1)), 
End_Date__c - Start_Date__c,
IF(
AND(Start_Date__c <= Date(Fiscal_Year__c, 3, 31), Start_Date__c >= Date(Fiscal_Year__c, 1, 1),
End_Date__c > Date(Fiscal_Year__c, 3, 31)), 
Date(Fiscal_Year__c, 3, 31) - Start_Date__c, 
IF(
AND(Start_Date__c < Date(Fiscal_Year__c, 1, 1),
End_Date__c <= Date(Fiscal_Year__c, 3, 31), End_Date__c >= Date(Fiscal_Year__c, 1, 1)), 
End_Date__c - Date(Fiscal_Year__c, 1, 1), 
IF(
AND(Start_Date__c < Date(Fiscal_Year__c, 1, 1),
End_Date__c > Date(Fiscal_Year__c, 3, 31)), 
Date(Fiscal_Year__c, 3, 31) - Date(Fiscal_Year__c, 1, 1), 
0))))

You just have to replace the quarter start date and quarter end date to make it work for other quarters.

For example,
replace Date(Fiscal_Year__c, 1, 1) with Date(Fiscal_Year__c, 4, 1) and Date(Fiscal_Year__c, 3, 31) with Date(Fiscal_Year__c, 6, 30) for Q2 and so on.

Keep me posted.
This was selected as the best answer
LBKLBK
By the way, if you have a way to fetch the Year number from one of the existing Date field in your record, replace Fiscal_Year__c field with Year(YOUR_DATE_FIELD__c).

Remember this field has to be of Date data type.
Travis Lee 1Travis Lee 1
Hey LBK,

Thanks for the quick reply and apologies for my delays here. I'll give this a shot and let you know how things go! Is there an email I can reach you at if I have more in-depth questions?

Thanks,

Travis
LBKLBK
Hey Travis,

Keep posting your queries here. It will build the forum for others too.

Let me know how it goes.
Alain CabonAlain Cabon
The problems of formulas around the number of days are always interesting to everyone: https://developer.salesforce.com/forums/ForumsMain?id=9060G000000XhyAQAS  (unsolved, bug in the documentation, too simplisitic)

If the LBK's solution gives a correct result (for the current year), I don't understand why he doesn't have the 30 pts of the best answer yet.

If the LBK's formula is wrong, it is also interesting for everyone to have the feedback here. (otherwise I have a feeling that there are always profiteers and ungrateful people on the forums)