Calculate number of months in a specific year between two dates.

Hi,

Why is the answer for 2012 exclusive of the start month, yet that for 2013 inclusive of the end month?

Regards

You can use the DATEDIF function to get the number of months between two dates.

Assuming your Start date is in cell B1 and End Date is in cell B2, then you can get the total number of month passed between these 2 dates using,

Formula:

Please Login or Register  to view this content.

However, do you want specifically to get the breakup of number of months passed across years? Can u be more specific with you exact need?

With your Start and End Dates in A1 and B1 respectively:

=13-MONTH(A1)

=MONTH(B1)

Regards

Or this

=DATEDIF(A1,EDATE(A1,13-MONTH(A1)+DAY(A1))-1,"m")

	A	B
1	6/1/2012	7
2	2/1/2013	11

Originally Posted by XOR LX

With your Start and End Dates in A1 and B1 respectively:

=13-MONTH(A1)

=MONTH(B1)

Regards

Originally Posted by AlKey

Or this

Formula:

Please Login or Register  to view this content.

	A	B
1	6/1/2012	7
2	2/1/2013	11

Hi,

Note that, XOR LX's formula gives you count of boxes sold for each month, whereas Alkey's formula can be used only for the start date. For the end date, you need to use the below formula :

Formula:

Please Login or Register  to view this content.

You need to prefix the DATEDIF formula with "13 - (minus)" for every completed year in the above formula.

My formula,

Formula:

Please Login or Register  to view this content.

Gives you the total boxes sold between start and end dates.

Originally Posted by Saarang84

Note that, XOR LX's formula gives you count of boxes sold for each month

Not sure what you mean - it gives a count of the number of months in each of the two years.

Regards

Originally Posted by XOR LX

Not sure what you mean - it gives a count of the number of months in each of the two years.

Regards

Oops... My mistake. As per OP's requirement, the OP wants to calculate the number of boxes sold in those months.

Then your formula should be changed as :

Formula:

Please Login or Register  to view this content.

where x denotes the number of boxes sold each month. Then, as per the example given below, the formula would calculate

Formula:

Please Login or Register  to view this content.

between Jun '12 till Dec '12 and

Formula:

Please Login or Register  to view this content.

in Jan '13 and Feb '13.

My formula also calculates the same and should be changed as

Formula:

Please Login or Register  to view this content.

where x is 10. so, 90 boxes in both cases.

I have the same question as the above but the answers do not address it.
In Col. A I have Contract ID, Col B a contract start date and in Col. C I have a contract end date.
I want to out put the number of months that occur in the specific years 2017, 2018, 2019, 2020,2021 and 2022 in each contract.
Example input (space delimited):

ContractID StartDate EndDate
AAA 01.04.2019 31.12.2033
BBB 25.09.2020 10.05.2033
CCC 06.06.2017 05.05.2020
DDD 01.04.2019 07.04.2022

Desired output (in Cols)(space delimited)
Yr(2017) Yr(2018) Yr(2019) Yr(2020) Yr(2021) Yr(2022)
0 0 8 12 12 12
0 0 0 3 12 12
6 12 12 5 0 0
0 0 4 12 12 4

Any suggestions?

Hello horanio. Welcome to the forum.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread