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

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/09/20

    Hi @Derek Meldrum

    Hope You are fine, could you please supply screen shot

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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



    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    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), "")))))



    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • @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
    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 : [email protected]

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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


    Thanks

  • Bassam Khalil
    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!

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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