Formula to return number of remaining days between two dates ignoring the number of three month periods

angel34 · Oct 30, 2017

[FONT=&quot]I was wondering if there is a formula in excel which returns the number of remaining days between 2 dates, ignoring the number of three month periods. Three month period need not be a standard calendar quarter i.e. from 1^st Jan to 31^st March. It can also be from 2^nd Feb to 1^st of May.[/FONT]
[FONT=&quot] [/FONT] [FONT=&quot]For example, if the 2 dates in A1 and B1 are 5/26/2016 and 10/30/2017, then starting from 5/26/2016, the last three month period (5^th one) would end on 8/25/2017. The remaining days up to 10/30/2017 from 8/25/2017 would be 66 days which I need the formula to return in C1[/FONT] [FONT=&quot] [/FONT] [FONT=&quot]Any help in this regard is much appreciated. Thanks and regards,[/FONT]

steve the fish · Oct 30, 2017

If you are doing what i think you are doing you then you need a slight rethink. What happens if your first date is 30th November? What is 3 months after that? There isnt a 29th February.

Kuljack · Oct 30, 2017

Depending on how you are quantifying your three month period you could try this:

=(E23-(E22+91))

E23 = the 10/30/2017 date
E22 = the 5/26/2017 date
91 = the integer that supports your math for what you captured as the result of a three month period from 5/26. If this varies, you could have this reference another cell dedicated to capturing that period in day

angel34 · Oct 30, 2017

Thanks to both of you for choosing to reply to my query. I agree to what Steve points out. In that case, 1st March could be taken as the end date, although, I agree, that may not be technically correct, but tolerance of 1 day is acceptable. Sorry, I'm not sure I understood what Kuljack stated. A three month period can be from 89 days (Feb-April) to 92 days (May-July). The start date given in my post (5/26/2016), was used, but the formula returns 430 instead of 66.

NARAYANK991 · Oct 30, 2017

Hi ,

See if the following array formula , to be entered using CTRL SHIFT ENTER , works :

=B1 - MAX(IF(EDATE(A1, {1,2,3,4,5,6,7,8,9,10}*3) <= B1, EDATE(A1, {1,2,3,4,5,6,7,8,9,10}*3))) + 1

angel34 · Oct 31, 2017

The solution works perfectly for all three month periods barring calendar quarters. As for example, for 4/1/2017 to 9/30/2017, the formula returns 92, whereas it should return "0'. Is that the way the formula is supposed to work? But calendar quarters are also nothing but three month periods, is there a way the formula could be tweaked to suit a calendar quarter?. I shall be very much thankful to receive a reply. Nevertheless, thank you very much for your kind help.

steve the fish · Oct 31, 2017

See if this does it:

=MAX(0,B1-EDATE(A1-1,3))

or this:

=MAX(0,B1-EDATE(A1,3)+1)

barry houdini · Oct 31, 2017

Hello angel34,

Try this formula

=B1-EDATE(A1,FLOOR(DATEDIF(A1,B1+1,"m"),3))+1

Format result cell as number

angel34 · Oct 31, 2017

As regards the post of Steve, unfortunately, none of the above two solutions work in my case.

NARAYANK991 · Oct 31, 2017

Hi ,

Barry's gives the results you want , I think.

Recheck your results with his , as well as this :

=B1 - MAX(IF(EDATE(A1, {0,1,2,3,4,5,6,7,8,9,10}*3) - 1 <= B1, EDATE(A1, {0,1,2,3,4,5,6,7,8,9,10}*3))) + 1

The above is an array formula , to be entered using CTRL SHIFT ENTER.

Formula to return number of remaining days between two dates ignoring the number of three month periods

angel34

Board Regular

Excel Facts

steve the fish

Well-known Member

Kuljack

Active Member

angel34

Board Regular

NARAYANK991

Board Regular

angel34

Board Regular

steve the fish

Well-known Member

barry houdini

MrExcel MVP

angel34

Board Regular

NARAYANK991

Board Regular

Similar threads

Forum statistics

Share this page

Formula to return number of remaining days between two dates ignoring the number of three month periods

Board Regular

Excel Facts

Well-known Member

Active Member

Board Regular

Board Regular

Board Regular

Well-known Member

MrExcel MVP

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock