find number of days per month between 2 dates

DDias

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

Melissa Boehl

@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?

DDias

thanks @Melissa Boehl for answering. Calendar days.

This is a yearly budget. not a work schedule

Melissa Boehl

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

DDias

@Melissa Boehl

please see below

DDias

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

Looking for the solution above. Any ideas?

Paul Newcome

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

didnt work.

Paul Newcome

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

@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

@Cleversheet THAT IS IT!

please show me the magic formulas :)

Cleversheet

@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

@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

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

Alex C.

@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

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