find number of days per month between 2 dates

Options
DDias
DDias ✭✭
edited 08/11/22 in Formulas and Functions

Hello,

i need help with the below I have many projects with cost that i need to divide: here is one line.

start Date 02/10/2022

end date 05/06/2022

cost : $35,000

Looking to project the average cost between those 2 dates per month.

solution: 86 days between start and end date

19 days in February x $406.97 ($35000/86)= $7,732

Jan:$0 cost

Feb: $7,732 cost

March : 31 days * 406.97

April : 30 days x $406.97

May: 6 days x $406.97


this formula in excel normally gives me that, but smartsheets doesnt work.

SUMPRODUCT(--(MONTH(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),B2))))=MONTH(C$1)))


«1

Answers

  • Melissa Boehl
    Melissa Boehl ✭✭✭✭✭✭
    Options

    @DDias This is going to be a combination of different functions combined to build the formula you need to get the results you want. Smartsheet is nothing like excel. Especially when it comes to formulas. I think that is the most common mistake that people make when they start using Smartsheet. Because it looks like a spreadsheet, new users automatically assume it works like excel, but it doesn't. First, we need to identify what days you want counted in between the 2 dates. Are you counting calendar days or working days?

    Melissa Boehl

    Smartsheet Architect | TurningPoint Energy

  • DDias
    DDias ✭✭
    edited 08/16/22
    Options

    thanks @Melissa Boehl for answering. Calendar days.

    This is a yearly budget. not a work schedule

  • Melissa Boehl
    Melissa Boehl ✭✭✭✭✭✭
    Options

    @DDias Do you have a screenshot you could share of the sheet? I just would like to see how you have it structured. You can scrub or blackout the data.

    Melissa Boehl

    Smartsheet Architect | TurningPoint Energy

  • DDias
    DDias ✭✭
    Options
  • DDias
    DDias ✭✭
    Options

    Hey there @Paul Newcome and @Andrée Starå

    Looking for the solution above. Any ideas?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Lets start with this and see where it gets you. Change the bold numbers to whichever month number column you have the formula in. So the below would go in June, moving it to July would mean changing the bold numbers to 7, so on and so forth.


    =IF(MONTH([Start Date]@row)< 6, IF(MONTH([End Date]@row) = 6, DAY([End Date]@row), IF(MONTH([End Date]@row)> 6, DAY(IFERROR(DATE(YEAR([End Date]@row), MONTH([End Date]@row) + 1, 1), YEAR([End Date]@row) + 1, 1, 1)) - 1))), IF(MONTH([Start Date]@row) = 6, (IFERROR(DATE(YEAR([End Date]@row), MONTH([End Date]@row) + 1, 1), YEAR([End Date]@row) + 1, 1, 1)) - 1) - [Start Date]@row)) * [$ Amount]@row

  • DDias
    DDias ✭✭
    Options
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    My apologies. It looks like I got ahead of myself and my brain and fingers weren't moving at the same speed.


    =IF(MONTH([Start Date]@row)< 6, IF(MONTH([End Date]@row) = 6, DAY([End Date]@row), IF(MONTH([End Date]@row)> 6, DAY(IFERROR(DATE(YEAR([End Date]@row), MONTH([End Date]@row) + 1, 1), YEAR([End Date]@row) + 1, 1, 1)) - 1))), IF(MONTH([Start Date]@row) = 6, (IFERROR(DATE(YEAR([End Date]@row), MONTH([End Date]@row) + 1, 1), DATE(YEAR([End Date]@row) + 1, 1, 1)) - 1) - [Start Date]@row)) * [$ Amount]@row

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭
    Options

    @DDias , if this screenshot looks like something you’d find helpful I can share the sheet with you.

    Note two things:

    1) It calculates the days in each month between any range of dates (you need to have a column for each applicable month).

    2) The [Billable Days] column formula can be adapted to eliminate weekends. (Would be more challenging to eliminate holidays but presumably possible.)

    In your case, if I understand the need correctly, you could either create corresponding separate columns to house your averages, or else simply use the formula/result for each count as the denominator in an average formula you adapt into the existing columns.

    Troy

  • DDias
    DDias ✭✭
    Options

    @Cleversheet THAT IS IT!

    please show me the magic formulas :)

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭
    edited 08/26/22
    Options

    @DDias, Here's the formula. The comparison values need to be changed for each column (eg, this is the [Days in Feb] column and it's looking for the value 2 in the eight bolded places; and the number of days in each month needs to be updated such as the 28 in the two bolded/italicized places):

    =IFERROR(IF(AND(MONTH([Start Date]@row) < 2, MONTH([End Date]@row) = 2), DAY([End Date]@row), IF(AND(MONTH([Start Date]@row) = 2, MONTH([End Date]@row) = 2), DAY([End Date]@row) - DAY([Start Date]@row), IF(AND(MONTH([Start Date]@row) < 2, MONTH([End Date]@row) > 2), 28, IF(AND(MONTH([Start Date]@row) = 2, MONTH([End Date]@row) > 2), 28 - DAY([Start Date]@row), "")))), "")

    Here's a playground version of the sheet.

    As noted earlier, you could plop that formula in as the denominator for the month/s you need to display the average, rather than have a separate column for just calcing the number of days in the month/s.

    For general reference here's a version of the sheet that contains a bunch of date-related formulas to show month name, day name, etc.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @DDias I am curious if my amended formula worked for you?


    One other note if you are going to use @Cleversheet's solution...

    In addition to the month reference bolded in the above comment, you will also need to adjust the number of days for each month (bolded below).

    =IFERROR(IF(AND(MONTH([Start Date]@row) < 2, MONTH([End Date]@row) = 2), DAY([End Date]@row), IF(AND(MONTH([Start Date]@row) = 2, MONTH([End Date]@row) = 2), DAY([End Date]@row) - DAY([Start Date]@row), IF(AND(MONTH([Start Date]@row) < 2, MONTH([End Date]@row) > 2), 28, IF(AND(MONTH([Start Date]@row) = 2, MONTH([End Date]@row) > 2), 28 - DAY([Start Date]@row), "")))), "")

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭
    Options

    Ah, yes, @Paul Newcome, thanks for catching that! I've fixed it in the comment now.

  • Alex C.
    Alex C. ✭✭
    edited 01/17/23
    Options

    @Cleversheet I've been looking for a solution just to calculate the days in each month between a range of dates and I'm so glad I found this. Thank you! However, I'm not sure it is calculating correctly. I needed to calculate January as well as returning a 0 instead of a blank if the count was out of range. However, the formula doesn't seem to be grabbing everything. This is the exact formula I used for Feb:

    =IFERROR(IF(AND(MONTH([Start Date]@row) < 2, MONTH([End Date]@row) = 2), DAY([End Date]@row), IF(AND(MONTH([Start Date]@row) = 2, MONTH([End Date]@row) = 2), DAY([End Date]@row) - DAY([Start Date]@row), IF(AND(MONTH([Start Date]@row) < 2, MONTH([End Date]@row) > 2), 28, IF(AND(MONTH([Start Date]@row) = 2, MONTH([End Date]@row) > 2), 28 - DAY([Start Date]@row), 0)))), "")


    Specifically, I believe it doesn't like anything with a start date last year but I don't know how to fix that.


  • Cleversheet
    Cleversheet ✭✭✭✭✭✭
    Options

    Two things jump quickly to mind:

    1. To display zeros instead of blanks, try replacing "" with 0 just before the final paren.
    2. To include any day counts involving Dec you'd need to add a [Days in Jan '22] column.

    Hope that helps.

    Troy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!