Number of months per year between 2 dates

Question

Hello,I would like to calcultate the number of months per year between 2 dates.My table is like this:ID---------------Year---------------Start date--------End date-----------------Result(my wish)150-------------2018--------------18/08/2018-------30/06/2020--------------5150-------------2019--------------18/08/2018-------30/06/2020--------------12150-------------2020--------------18/08/2018-------30/06/2020--------------6Thanks a lot for your helpN.

hotchilli · Answer

What is the logic here? Where do 5, 12, 6 come from?

nbaudry · Answer

The logic and the goal is to make a distribution of the load per month, the loads are filled per year.

(I just realized a mistake in the column year ... sorry ... bad copy/paste)

ID------Year----Start date--------End date--------Annual Load--------Result(my wish)
150----2018----18/08/2018-------30/06/2020----100------------------5
150----2019----18/08/2018-------30/06/2020----50-------------------12
150--- 2020----18/08/2018-------30/06/2020----120------------------6

5 months: Since the 18/08/2018 to 31/12/2018

12, 6 ...

hotchilli · Answer

Still not clear...ID------Year----Start date--------End date--------Annual Load--------Result(my wish)150----2018----18/08/2018-------30/06/2020----100------------------5150----2019----18/08/2018-------30/06/2020----50-------------------12150--- 2020----18/08/2018-------30/06/2020----120------------------6&nbsp;&nbsp;5 months: Since the 18/08/2018 to 31/12/2018&lt;----------------------- why 31/12/201812, 6 ...&nbsp; &lt;------------- please explain these

nbaudry · Answer

These 3 lines are for the same project:&nbsp;ID------Year----Start date--------End date--------Annual Load--------Result(my wish)150----2018----18/08/2018-------30/06/2020----100------------------5The start date for the project is&nbsp;18/08/2018 and the end&nbsp;30/06/2020. The project manager filled a load of 100 days for 2018. I want to make a distribution of this load for the month for 2018. That means 100/5 =&gt; 20 days for, August 2018, September 2018, November 2018 and December 2018.150----2019----18/08/2018-------30/06/2020----50-------------------12For this example the load must be distributed on the year of 2019 because 01/01/2019 - 31/12/2019 is inside of the start and end date, so 50/12 = 4,17 days150--- 2020----18/08/2018-------30/06/2020----120------------------6Same thing but only on 6 months because the end date.I hope it's clear now :-)

Forum Discussion

Number of months per year between 2 dates

4 Replies

Related content

Month name to Month Numbers

FORMAT() returns Month Number

Power BI: Month - Sort By Month Number

month name to number

Get number of days in displayed month.