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.
ShowAll Questionssorted byDate Posted jenny 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
) Best Answer chosen by jenny jade VineetKumar
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 Swaraj 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 jade
No ,it did not work VineetKumar
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 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. VineetKumar
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 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 VineetKumar
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 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' VineetKumar
Still showing the wrong value? jenny 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 VineetKumar
I guess it did got captured but then you changed it to yours.. :( jenny 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. VineetKumar
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 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