+ Start a Discussion
Amanda JonesAmanda Jones 

How to set a default calculation for a field based on length of time?

Hello!

I have a question that I am pretty sure is an easy fix, only I can't seem to wrap my brain around the solution, so I am deferring to bigger brains than mine. :)

I have two formula fields. One calculates the number of months of a mission trip and the other returns a cost based on the number of months. what I need is a modification to one or both formulas that will return the cost for One month even if the dates calculate to Less than 30 days.

Here is the formula that caculates the length of time
(EndDate - StartDate +1)/30

Here is the formula that calculates the cost for the length of time:

STIMonths__c *CASE(TEXT(Country__c),"Argentina", 750.00,
"Bolivia", 625.00,
"Colombia",650.00,
"Costa Rica", 685.00,
"El Salvador", 685.00,
"Guatemala", 685.00,
"Tijuana, MX", 650.00,
"Tuxpan, MX", 650.00,
"Nicaragua", 685.00,
"Peru", 650.00, NULL)

Any guidance is hugely appreciated!
Shashikant SharmaShashikant Sharma
Id STIMonths__c  is formula => (EndDate - StartDate +1)/30

Could you give an example of input and expected out put which you are not getting from current formulas.
Amanda JonesAmanda Jones
Hello Shashikant,

If the start and end dates do not total total 30 days or more, the STI Monthly Cost field returns a zero value. I need the STI Monthly Cost field to return the cost for one month, even if the start and end date equate to LESS THAN one month.

Please see screen shot. For the result to turn out correctly,  I need the STI Daily Cost field to return a value of zero if the start and end dates equate to less than 30 days. And I need the STI Monthly Cost field to return the value for one month if the start and end dates equate to less than 30 days.

screenshot
kaustav goswamikaustav goswami
Can you try this - 
 
IF(STIMonths__c < 1, 
	CASE(TEXT(Country__c),
		"Argentina", 750.00,
		"Bolivia", 625.00,
		"Colombia",650.00,
		"Costa Rica", 685.00,
		"El Salvador", 685.00,
		"Guatemala", 685.00,
		"Tijuana, MX", 650.00,
		"Tuxpan, MX", 650.00,
		"Nicaragua", 685.00,
		"Peru", 650.00, NULL),
	STIMonths__c * CASE(TEXT(Country__c),
		"Argentina", 750.00,
		"Bolivia", 625.00,
		"Colombia",650.00,
		"Costa Rica", 685.00,
		"El Salvador", 685.00,
		"Guatemala", 685.00,
		"Tijuana, MX", 650.00,
		"Tuxpan, MX", 650.00,
		"Nicaragua", 685.00,
		"Peru", 650.00, NULL)
)

If STI Months is < 1 the nreturn the value of case else multiply and return?

Thanks,
Kaustav
Shashikant SharmaShashikant Sharma
Change your first forumula field STIMonths__c  to this :

IF( MOD(( EndDate - StartDate + 1), 30) > 0, 
    (( EndDate - StartDate + 1)/30 ) + 1 , ( EndDate - StartDate + 1)/30 )

It should work fine then.