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
jenny jadejenny jade 

Subscription Months+days

Hi 

I need to calulate the time between two dates (start date and end date) so far Its calculating months alone but I need to calculate months+days menaing if start date =7/18/2016 and end date = 7/18/2017 its 12months which is good but
if start date =7/18/2016  end date = 7/19/2017 it should actually be 12.01(12months ,1 day)

the formula that I am using is :
IF(NOT(ISBLANK(EndDate__c)) && 
NOT(ISBLANK(StartDate__c)) 
,(((YEAR(EndDate__c) - YEAR(StartDate__c) - 1) *12) + (12 - MONTH(StartDate__c) +1) + MONTH(EndDate__c) 
- 1+ IF(DAY(EndDate__c) > DAY(StartDate__c),1,0)), 
null 
)

Please help
Best Answer chosen by jenny jade
VineetKumarVineetKumar
You need to replace my formulae with your date fields.
Anyways use the below one 
IF(NOT(ISBLANK(StartDate__c)) && 
NOT(ISBLANK(EndDate__c)) 
, TEXT(((YEAR(EndDate__c) - YEAR(StartDate__c) - 1) *12) + (12 - MONTH(StartDate__c) +1) + MONTH(EndDate__c)
- 1)+','+TEXT(IF(DAY(StartDate__c) > DAY(EndDate__c), DAY(StartDate__c) - DAY(EndDate__c), DAY(EndDate__c) - DAY(StartDate__c))), 
null 
)
Check for any missing brackets.
Assuming your start and end date are of type Date

All Answers

Swaraj Behera 7Swaraj Behera 7
Hi Jenny,
Can you please try below formula  
IF(EndDate__c < StartDate__c, 0,1)
*
(IF(EndDate__c - StartDate__c >=365, 
  12 * ((YEAR(EndDate__c)- YEAR(StartDate__c)
  -
  IF(MONTH(StartDate__c) > MONTH(EndDate__c), 1, 0))),0)
+

IF(MONTH(StartDate__c) = MONTH(EndDate__c),
  IF(DAY(StartDate__c) < DAY(EndDate__c), 1, 
    IF(EndDate__c - StartDate__c  < 365 &&  EndDate__c  <>  StartDate__c,12,0)),0)

+

IF(MONTH(StartDate__c) > MONTH(EndDate__c),    
  12-(MONTH(StartDate__c) - MONTH(EndDate__c))
  +    
  IF(DAY(StartDate__c) < DAY(EndDate__c),1, 0),0)

+

IF(MONTH(StartDate__c) < MONTH (EndDate__c),
  (MONTH(EndDate__c) - MONTH(StartDate__c))
  +
  IF(DAY(StartDate__c) >= DAY(EndDate__c), 0, 1),0))

 
jenny jadejenny jade
No ,it did not work
VineetKumarVineetKumar
Check this, this will calculate the number of days skipping the weekends (Sat/Sun) 
CASE(MOD(DATEVALUE(EndDate__c) - DATE(1985,6,24),7), 
0 , CASE( MOD( DATEVALUE(StartDate__c) - DATEVALUE(EndDate__c),7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( DATEVALUE(StartDate__c) - DATEVALUE(EndDate__c),7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( DATEVALUE(StartDate__c) - DATEVALUE(EndDate__c),7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( DATEVALUE(StartDate__c) - DATEVALUE(EndDate__c),7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( DATEVALUE(StartDate__c) - DATEVALUE(EndDate__c) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( DATEVALUE(StartDate__c) - DATEVALUE(EndDate__c),7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( DATEVALUE(StartDate__c) - DATEVALUE(EndDate__c),7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR(( DATEVALUE(StartDate__c) - DATEVALUE(EndDate__c))/7)*5)
jenny jadejenny jade
Hi Vineet, I got this error message Error: Incorrect argument type for function 'DATEVALUE()'. and we want even the weekends to be calculated . Please Suggest.
VineetKumarVineetKumar
Reworked your formula
Return type of the formula is TEXT 
IF(NOT(ISBLANK(CreatedDate)) && 
NOT(ISBLANK(TODAY())) 
, TEXT(((YEAR(TODAY()) - YEAR(DATEVALUE(CreatedDate)) - 1) *12) + (12 - MONTH(DATEVALUE(CreatedDate)) +1) + MONTH(TODAY())
- 1)+','+TEXT(IF(DAY(DATEVALUE(CreatedDate)) > DAY(TODAY()), DAY(DATEVALUE(CreatedDate)) - DAY(TODAY()), DAY(TODAY()) - DAY(DATEVALUE(CreatedDate)))), 
null 
)
jenny jadejenny jade
Hi Vineet,

I changed my return type to Text and copy pasted the formula you gave the result it gave me is 0,1
my startdate =7/18/2016
end date=7/18/2017, in fact irrespective of any dates its giving output as 0,1

 
VineetKumarVineetKumar
You need to replace my formulae with your date fields.
Anyways use the below one 
IF(NOT(ISBLANK(StartDate__c)) && 
NOT(ISBLANK(EndDate__c)) 
, TEXT(((YEAR(EndDate__c) - YEAR(StartDate__c) - 1) *12) + (12 - MONTH(StartDate__c) +1) + MONTH(EndDate__c)
- 1)+','+TEXT(IF(DAY(StartDate__c) > DAY(EndDate__c), DAY(StartDate__c) - DAY(EndDate__c), DAY(EndDate__c) - DAY(StartDate__c))), 
null 
)
Check for any missing brackets.
Assuming your start and end date are of type Date
This was selected as the best answer
jenny jadejenny jade
Hi Vineet,

I edited the date fields as per my requirement and added a missing bracket .Yes,my start and end date fields are of type date
and my return type for the formula is 'Text'
VineetKumarVineetKumar
Still showing the wrong value?
jenny jadejenny jade
Its Perfect now!!

IF(NOT(ISBLANK(Start_Date__c)) && 
NOT(ISBLANK(End_Date__c)) 
, TEXT(((YEAR(End_Date__c) - YEAR(Start_Date__c) - 1) *12) + (12 - MONTH(Start_Date__c) +1) + MONTH(End_Date__c) 
- 1)+','+TEXT(IF(DAY(Start_Date__c) > DAY(End_Date__c), DAY(Start_Date__c) - DAY(End_Date__c), DAY(End_Date__c) - DAY(Start_Date__c))), 
null 
)


I even market the answer as best answer in my earlier reply ,I don't know wht that did not get captured 
VineetKumarVineetKumar
I guess it did got captured but then you changed it to yours.. :(
jenny jadejenny jade
Hi Vineet,

Sorry for troubling you this many times.But ,you are the only goto person for me 
I gave start date = 1/1/2015
end date = 12/31/2015

the answer it gave me is 11.30 but it shd actually be 12 months as  it a full one year starting from Jan1st to Dec 31st.
VineetKumarVineetKumar
I guess the output is correct, because the above formula gives the days between the start date and the end date.
So, for start date = 1/1/2015, end date = 12/31/2015 : it will 11 months and 30 days.

Correct me if I'm wrong.
jenny jadejenny jade
Could you please tweak the formula for me.So, it would give 12 months 
if start date = 1/1/2015
end date = 12/31/2015