Calculate Quarters and remaining days between two dates

[Deepak Sharma]

Hi All,

How can we get the desired result. If I have two cells like this :
A1 B1
15 August 2016 31 March 2017

In C1 - Result should be like this as below :-
2 Quarters and 45 Days

I have putted this formula but it gives quarters only :-
=FLOOR(((YEAR(B2)*12+MONTH(B2))-(YEAR(A2)*12+MONTH(A2)))/3,1)&" Qtrs"

 

[NARAYANK991]

Hi ,

If you think that the formula you have posted for returning the number of quarters , is reliable , then try this extension :

=FLOOR(((YEAR(B2)*12+MONTH(B2))-(YEAR(A2)*12+MONTH(A2)))/3,1) & " Qtrs. , " & B2 - EDATE(A2, 3 * FLOOR(((YEAR(B2)*12+MONTH(B2))-(YEAR(A2)*12+MONTH(A2)))/3,1)) + 1 & " days"

Narayan

 

[bosco_yip]

Another option :

=INT(DATEDIF(A1,B1,"m")/3)&" Quarters and "&B1-EDATE(A1,INT(DATEDIF(A1,B1,"m")/3)*3)+1&" Days"

Regards
Bosco

 

[Deepak Sharma]

Hi Narayan, Thanks for your time.
Yeah you are right...it's not giving exact quarters when I tried following dates with this formula. It shows below :-
A1 B1 C1
01 January 2018 - 31 December 2018 = 3 Qtrs. & 92 days

Is there any perfect formula to calculate like this ? Please suggest !!

Regards,

 

[Deepak Sharma]

Another option :

=INT(DATEDIF(A1,B1,"m")/3)&" Quarters and "&B1-EDATE(A1,INT(DATEDIF(A1,B1,"m")/3)*3)+1&" Days"

Regards
Bosco

Thanks Bosco.....Could you please tell whats the signification of +1 at the end ?

 

[bosco_yip]

Thanks Bosco.....Could you please tell whats the signification of +1 at the end ?

Duration (days) = End date - Start date + 1

Regards

 

[Deepak Sharma]

Thanks Bosco.......Thanks Narayan......For your time and help....

Thank you so much !!!!!!!!!