Task crossing seperate months - counting the days in each month

Derek Meldrum

Hi,

I have a sheet which in which we have tasks both starting and ending in the same month as well as some which start one month and end in the next.

I am trying to calculate the number of days which fall in each month.

Is there any way to do this?

Thanks

Derek

Bassam Khalil

Hi @Derek Meldrum

Hope You are fine, could you please supply screen shot

Derek Meldrum

Hi @Bassam.M Khalil,

Hope you are well. thanks for the reply.

The majority of our tasks fall into the same month, however there are some which cross over two months depending on the start date. I am really unsure where to start with this one.

I am hoping that I can display the days per month as a graph in my dashboard as a utilisation indicator.

I have already set this up to show invoicing value per month however I am being tasked with showing the months total days. 

Appreciate any help you can offer.

Thanks

Derek

Bassam Khalil

Hi @Derek Meldrum

Sorry for delay, i prepared for you sample sheet and you can use the following formula for calculation

1- In-Jan-Days =IF([Start Date]@row > DATE(2021, 1, 31), "", IF(AND([Start Date]@row >= DATE(2021, 1, 1), [Due Date]@row <= DATE(2021, 1, 31)), [Due Date]@row - [Start Date]@row, IF(AND([Start Date]@row < DATE(2021, 1, 1), [Due Date]@row <= DATE(2021, 1, 31), [Due Date]@row > DATE(2021, 1, 1)), [Due Date]@row - DATE(2021, 1, 1), IF(AND([Start Date]@row >= DATE(2021, 1, 1), [Start Date]@row < DATE(2021, 1, 31), [Due Date]@row > DATE(2021, 1, 31)), DATE(2021, 1, 31) - [Start Date]@row, IF(AND([Start Date]@row < DATE(2021, 1, 1), [Due Date]@row > DATE(2021, 1, 31)), DATE(2021, 1, 31) - DATE(2021, 1, 1), "")))))

2-In-Feb-Days =IF([Start Date]@row > DATE(2021, 2, 28), "", IF(AND([Start Date]@row >= DATE(2021, 2, 1), [Due Date]@row <= DATE(2021, 2, 28)), [Due Date]@row - [Start Date]@row, IF(AND([Start Date]@row < DATE(2021, 2, 1), [Due Date]@row <= DATE(2021, 2, 28), [Due Date]@row > DATE(2021, 2, 1)), [Due Date]@row - DATE(2021, 2, 1), IF(AND([Start Date]@row >= DATE(2021, 2, 1), [Start Date]@row < DATE(2021, 2, 28), [Due Date]@row > DATE(2021, 2, 28)), DATE(2021, 2, 28) - [Start Date]@row, IF(AND([Start Date]@row < DATE(2021, 2, 1), [Due Date]@row > DATE(2021, 2, 28)), DATE(2021, 2, 28) - DATE(2021, 2, 1), "")))))

and you can change the start and finish date for each month using In-Feb-Days formula, following screen shot show the sample

Bassam Khalil

Hi @Derek Meldrum

i add for you formula for March for more details

In-Mar-Days =IF([Start Date]@row > DATE(2021, 3, 31), "", IF(AND([Start Date]@row >= DATE(2021, 3, 1), [Due Date]@row <= DATE(2021, 3, 31)), [Due Date]@row - [Start Date]@row, IF(AND([Start Date]@row < DATE(2021, 3, 1), [Due Date]@row <= DATE(2021, 3, 31), [Due Date]@row > DATE(2021, 3, 1)), [Due Date]@row - DATE(2021, 3, 1), IF(AND([Start Date]@row >= DATE(2021, 3, 1), [Start Date]@row < DATE(2021, 3, 31), [Due Date]@row > DATE(2021, 3, 31)), DATE(2021, 3, 31) - [Start Date]@row, IF(AND([Start Date]@row < DATE(2021, 3, 1), [Due Date]@row > DATE(2021, 3, 31)), DATE(2021, 3, 31) - DATE(2021, 3, 1), "")))))

Derek Meldrum

@Bassam.M Khalil, brilliant! this works absolutely perfectly! really appreciate the help.

I have tried to extend it to include my final step of this.  Some tasks have duplicate attendance, I set an additional column  to multiply the qty by the days, this works unless there are zero days in that month, Im guessing I need to wrap the =sum in and IFERROR but im not getting the syntax right with that.

Unless there is a method to add this to your formula

currently summing using;

=SUM([December days]@row * [qty engineers]@row)

Thanks

Derek

Bassam Khalil

Hi @Derek Meldrum 

If you can share your sheet after you remove sensitive date to my Email maybe i could help you

My Email : bassam.k@mobilproject.it

Derek Meldrum

Hi @Bassam.M Khalil thank you, I have sent this to you

Thanks

Bassam Khalil

Hi @Derek Meldrum

 Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Brett Haylock

Hi Bassam

I have a query regarding the above solution as I am trying to do the same thing.

One thing I noticed is the formula output number of days is not inclusive of either one of the start or end dates (i.e. it is one day less than I would expect).

Is there a way the formula can be changed to account for this? I ensured all ranges had been changed to "equal to or less/greater than" but this did not seem to solve the situation.

Kind Regards

Brett