Ask Search:
Allison HumphreyAllison Humphrey 

Formula to count number of days til date if in same month, otherwise number of days to last day of month

Hi there - 
I have a current formula to calculate the number of days from TODAY to a specific date:     EstimatedLaunchDate__c - TODAY()
But...I need an OR statement, so that: 

IF <EstimatedLaunchDate__c> is in the current month, THEN calculate number of days from TODAY.   EstimatedLaunchDate__c - TODAY()
OR
IF<EstimatedLaunchDate__c> is in a future month, THEN calculate number of days remaining in current month.  DaysinCurrentMonth__c - TODAY()

Any help would be much appreciated.  
Thanks!
 
Best Answer chosen by Allison Humphrey
Steve MolisSteve Molis
So something like a Formula(Number) field like this?
MIN( 
EstimatedLaunchDate__c - TODAY() ,
(ADDMONTHS(DATE(YEAR(TODAY()),MONTH(TODAY()), 01), 1) - 1)  - TODAY() 
)

 

All Answers

Steve MolisSteve Molis
So something like a Formula(Number) field like this?
MIN( 
EstimatedLaunchDate__c - TODAY() ,
(ADDMONTHS(DATE(YEAR(TODAY()),MONTH(TODAY()), 01), 1) - 1)  - TODAY() 
)

 
This was selected as the best answer
Allison HumphreyAllison Humphrey
Hi Steve - 
Thanks for your response, this formula worked great.  I had figured it out but it was much longer than your suggestion.  
MY VERSION:  
IF( 
MONTH(Estimated_Launch_Date__c) == MONTH(TODAY()), 
(DAY(Estimated_Launch_Date__c )) - (DAY(TODAY())), (Daysincurrentmonth__c - (DAY(TODAY())) 
)

YOUR VERSION:
MIN( 
EstimatedLaunchDate__c - TODAY() ,
(ADDMONTHS(DATE(YEAR(TODAY()),MONTH(TODAY()), 01), 1) - 1)  - TODAY() 
)
Can you walk me through the breakdown of your formula, how it's working?  

The other question that I have is....  I had created another field to be able to calculate the number of days in a given month to calculate the price per day.  I'm thinking with your formula, I could do it without that field.  
So right now this is my overall formula:
((Unit_Price__c / Daysincurrentmonth__c) * 
MIN( 
Estimated_Launch_Date__c - TODAY() , 
(ADDMONTHS(DATE(YEAR(TODAY()),MONTH(TODAY()), 01), 1) - 1) - TODAY() 
))
Is there a better way to calculate it without the additional field?  Or is it better to just have the field?  
Thanks so much for your help.
Steve MolisSteve Molis
Hi Allison, 

I'll try to describe it the best I can. 

Starting tith the MIN Function.  MIN looks at a list on numbers and finds the smallest one.  The Numbers can be just "numbers", or they can be the result of Math the you're doing within the MIN Formula 

For example:
MIN( 45 , 30 , 10, 50 , 25)
will retun the number 10 
 
MIN( ( 8 + 3 ) , ( 30 / 2 ) , 10 , ( 3 * 3) , 25 )
will return the number 9   ( 3 * 3 )

In your Formula you're basically counting the number of Days between the EstimatedLaunchDate__c - TODAY()  and the number of Days between the Last Day of the Month - TODAY()  and taking wihichever number is smaller (and that's what the MIN is doing in my formula)   

This 
(ADDMONTHS(DATE(YEAR(TODAY()),MONTH(TODAY()), 01), 1) - 1)
Gives me the Last Day of the current month, no matter what month or year it is
 
Allison HumphreyAllison Humphrey
Thanks Steve, I really appreciate the help and breakdown explaination.  
As is tradition... I owe you a beer.  :)
You Rock!
Steve MolisSteve Molis
No problem Allison , I'll put it on your tab ;-D 
Allison HumphreyAllison Humphrey
I need a little more help on this formula, please. :)
This is my current formula for RealizableRevenue__c: 

((Unit_Price__c) / (DAY(ADDMONTHS(DATE(YEAR(TODAY()),MONTH(TODAY()), 01), 1) - 1))) 

(MIN( 
Estimated_Launch_Date__c - TODAY() , 
(ADDMONTHS(DATE(YEAR(TODAY()),MONTH(TODAY()), 01), 1) - 1) - TODAY() 
)) 
)
________________________
NOW... I think I mayneed to add an IF statement to the above formula to evaluate the following:

I need to look at what month TODAY() is in, then compare that to the month in the Estimated Launch Date field.  Then calculate item 1 or 2:
  1. If they are in the same month...Then calculate how many days are left in the month from the Estimated Launch Date.  So if it's 4/20, we would have 10 days left in the month so we would take the unit price / by # of Days in that month, then multiply it by the 10 days.
  2. If the Estimated Launch date is in a different month, then wait until the first day of that month, calculate the amount per day.  So if the Estimated Launch date is 5/13, calculate the daily amount (Unit Price / 31 days in May), calculate the number of days from the launch date to the last day of the month (31-13=18) and then multiply by the daily rate.
Thanks in advance for the help :)
Steve MolisSteve Molis
Okay, let me see what I can do (no promises)
Allison HumphreyAllison Humphrey
Hi Steve - 
Were you able to come up with any type of solution for this?  Let me know.
Thanks again for your help.
 
Steve MolisSteve Molis
Hi Allison,

Sorry, "work" work got crazy, the company that I work for got sold, then the holidays, etc, etc...  I've gotta reset and try to take a look at it if and when things calm down
Allison HumphreyAllison Humphrey
Ok. Thanks for letting me know.