# Task crossing seperate months - counting the days in each month

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

• ✭✭✭✭✭✭
edited 12/09/20

Hi @Derek Meldrum

Hope You are fine, could you please supply screen shot

bassam.khalil2009@gmail.com

• 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.

Thanks

Derek

• ✭✭✭✭✭✭

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.khalil2009@gmail.com

• ✭✭✭✭✭✭

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), "")))))

bassam.khalil2009@gmail.com

• @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.

currently summing using;

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

Thanks

Derek

• ✭✭✭✭✭✭

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

bassam.khalil2009@gmail.com

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

Thanks

• ✭✭✭✭✭✭

Hi @Derek Meldrum

bassam.khalil2009@gmail.com

• 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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!