Suggestion related to months formula

[Tom22]

Hi Guys,

Need your expert suggestion here.

I receive lot of data which states work starts at this date and finish at this date and I need to find out month in between these.

I worked on one formula but this doesn't give accurate result every time.

I am attaching one file with my formula and it is not giving accurate results in row one.

Another rule which we keep in mind anything less than 15th of any month will count under those month only, where any thing equals and more than 15th will move to next month.

E.g. if start date is 2/17/2021 and end date 5/31/2021, then our result should be 3 months but if start date is 2/13/2021 with same end date, result will be 4 months.

Looking forward to your comments/help.

Thanks

 

[GraH - Guido]

Your example contradicts the rule, does it not?
If day of start date is less then 15 you do not need to add 1 up to the result. Yet that is what you do in the example where the daynumber is 12. Which one of both is it?

 

[Tom22]

Your example contradicts the rule, does it not?
If day of start date is less then 15 you do not need to add 1 up to the result. Yet that is what you do in the example where the daynumber is 12. Which one of both is it?

Sorry GraH, didn't get you....sorry again for confusion...can you please elaborate a little bit

 

[GraH - Guido]

You can try =DATEDIF(E5,F5,"m")+(DAY(E5)>=15)

 

[Tom22]

Thanks Grah but this is not working exactly what I though....not giving correct output...attached file with your formula

 

[shrivallabha]

Can you post a sample where the formulas (yours as well as Guido's) do not work with the results expected? As of now, for the example you used i.e.

E.g. if start date is 2/17/2021 and end date 5/31/2021, then our result should be 3 months but if start date is 2/13/2021 with same end date, result will be 4 months.

your own formula works.

 

[GraH - Guido]

Another rule which we keep in mind anything less than 15th of any month will count under those month only, where any thing equals and more than 15th will move to next month.

E.g. if start date is 2/17/2021 and end date 5/31/2021, then our result should be 3 months but if start date is 2/13/2021 with same end date, result will be 4 months.

Hi Tom

This part DATEDIF(E5,F5,"m") gives the number of complete months in the period.
Then +(DAY(E5)>=15) adds 1 if the day of the start date equals 15 or more.

So I've provided the logic how it can be done. Up to you to tweak according to your rule. Which to me was contradictory to your example. Hence I asked to clarify, but you did not get the point as you said.

For future posts you may want to consider this tip. It truly helps if you provide all possible scenarios and for each the expected outcome. Highlighting where your formula gives a wrong result.

 

[Tom22]

I have tried to give different scenarios in the attached file.

1st case-start date 1/1/2021 and end date 6/30/2022, which we have total number of months 18 but for 2021 we have 12 months and 6 months belongs to 2022, where as formula suggested by Grah shows 17 months in 2021 and -1 in 2022.

2nd case- start date 2/14/2021 and end date 2/14/2022- so in ideal world ,we should have total number of months 12 months, where as start date is less than 15 hence we consider feb month as well....so in 2021 we should have 11 months and 1 month in 2022

3rd case- start date 2/15/2021 and end date 2/15/2022- so in ideal world ,we should have total number of months 12 months, where as start date is equal or more than 15 hence we will not consider Feb month....so in 2021 we should have 10 months and 2 month in 2022.

I hope, this time I am able to explain what is needed.

Please refer to attached file Book3 or you can refer to my original formula in Book1 above
Thanks

 

[GraH - Guido]

Thanks for the additional scenarios.
Say start is 1 st of Jan and end is 1st of feb. Would that be 1 or 2 months? If I add 2 months to Jan I end up in March.
My formula does not return the number of months for a particular year, but the the Total months in the given period. Do you need those intermediate results?
I'm without Excel for the moment and I can't open the workbook on my phone.

 

[shrivallabha]

I am still struggling to understand the issue here. Here's one example based on the understanding that you need formula to manage year-wise break up.

Formula looks like below which is just to ensure that we have a number closer to rounding.
=ROUND(DAYS360(MAX(EOMONTH($E5,(DAY($E5)>=15)-1),DATE(H$4,1,1)),MIN(DATE(H$4,12,31),EOMONTH($F5,(DAY($F5)>=15)-1)))/30,0)

 

[Tom22]

Thanks for the additional scenarios.
Say start is 1 st of Jan and end is 1st of feb. Would that be 1 or 2 months? If I add 2 months to Jan I end up in March.
My formula does not return the number of months for a particular year, but the the Total months in the given period. Do you need those intermediate results?
I'm without Excel for the moment and I can't open the workbook on my phone.

It should be 1 month.....as in feb it is less than 15 hence we will not consider it

 

[Tom22]

I am still struggling to understand the issue here. Here's one example based on the understanding that you need formula to manage year-wise break up.

Formula looks like below which is just to ensure that we have a number closer to rounding.
=ROUND(DAYS360(MAX(EOMONTH($E5,(DAY($E5)>=15)-1),DATE(H$4,1,1)),MIN(DATE(H$4,12,31),EOMONTH($F5,(DAY($F5)>=15)-1)))/30,0)

thanks Shrivallabha…..your formula working fine for some instance.....like in attached file cell I8 showing -2 for 2022 year for date range 2/1/2021 to 10/31/2021

 

[deciog]

Tom22

Helping, check if this is how you want it

Decio

 

[shrivallabha]

Obviously, it needs to account for non-intersecting phases of years which can be done by adding another MAX to the mix like below which is highlighted in red:

=ROUND(MAX(DAYS360(MAX(EOMONTH($E5,(DAY($E5)>=15)-1),DATE(H$4,1,1)),MIN(DATE(H$4,12,31),EOMONTH($F5,(DAY($F5)>=15)-1))),0)/30,0)

 

[Peter Bartholomew]

I haven't followed this thread, but what comes to mind is using a second DATEDIF to round up part months where needed
= DATEDIF(start, end,"M") +
(DATEDIF(start, end,"MD") > 14)