Count Days in Month across Years

Johanna

I am looking to be able to count the dates in a month between a campaign start and end date that crosses years so that I can project the monthly spend amounts. I am close in that I created monthly columns and am using this formula to count the days within the flights in each month:

IFERROR(IF(AND(MONTH([Flight Start]@row) < 1, MONTH([Flight End]@row) = 1), DAY([Flight End]@row), IF(AND(MONTH([Flight Start]@row) = 1, MONTH([Flight End]@row) = 1), DAY([Flight End]@row) - DAY([Flight Start]@row), IF(AND(MONTH([Flight Start]@row) < 1, MONTH([Flight End]@row) > 1), 31, IF(AND(MONTH([Flight Start]@row) = 1, MONTH([Flight End]@row) > 1), 31 - DAY([Flight Start]@row), "")))), "")

The issue is that it only works within the calendar year and I have flights that cross years. You can see in the example below that row 3 is giving the same result for January as row 2.

Any help appreciated!

Genevieve P.

Hi @Johanna

Try adding this to the beginning of your formula:

=IFERROR(VALUE((YEAR([Flight End]@row) - YEAR([Flight Start]@row)) * IF(AND([Flight End]@row - [Flight Start]@row < 365, OR(MONTH([Flight End]@row) = 1, MONTH([Flight Start]@row) = 1)), 0, 31) + IF(AND(OR(MONTH([Flight Start]@row) < 1, YEAR([Flight Start]@row) < YEAR([Flight End]@row)), MONTH([Flight End]@row) = 1), DAY([Flight End]@row), IF(AND(MONTH([Flight Start]@row) = 1, MONTH([Flight End]@row) = 1), DAY([Flight End]@row) - DAY([Flight Start]@row), IF(AND(MONTH([Flight Start]@row) < 1, MONTH([Flight End]@row) > 1), 31, IF(AND(MONTH([Flight Start]@row) = 1, OR(MONTH([Flight End]@row) > 1, YEAR([Flight End]@row) > YEAR([Flight Start]@row))), 31 - DAY([Flight Start]@row), ""))))), "")

This checks to see how many years are identified between your dates and multiplies it by the number of days in that month. If it's the same year, this will be 0 * 31. If there are two years, then it's 2 * 31 + your formula.

I had to add in another IF statement for those flights that cross over a year but are actually under a year in terms of length, so we're not adding in 31 days when it doesn't cross over the right number of months:

* IF(AND([Flight End]@row - [Flight Start]@row < 365, MONTH([Flight End]@row) = 1), 0, 31)

There are a few other additions comparing the YEARs in there as well. When you're adjusting this for the other months, for example February, here are the numbers in bold to change out:

=IFERROR(VALUE((YEAR([Flight End]@row) - YEAR([Flight Start]@row)) * IF(AND([Flight End]@row - [Flight Start]@row < 365, OR(MONTH([Flight End]@row) = 2, MONTH([Flight Start]@row) = 2)), 0, 28) + IF(AND(OR(MONTH([Flight Start]@row) < 2, YEAR([Flight Start]@row) < YEAR([Flight End]@row)), MONTH([Flight End]@row) = 2), DAY([Flight End]@row), IF(AND(MONTH([Flight Start]@row) = 2, MONTH([Flight End]@row) = 2), DAY([Flight End]@row) - DAY([Flight Start]@row), IF(AND(MONTH([Flight Start]@row) < 2, MONTH([Flight End]@row) > 2), 28, IF(AND(MONTH([Flight Start]@row) = 2, OR(MONTH([Flight End]@row) > 2, YEAR([Flight End]@row) > YEAR([Flight Start]@row))), 28 - DAY([Flight Start]@row), ""))))), "")

E.g. for March:

=IFERROR(VALUE((YEAR([Flight End]@row) - YEAR([Flight Start]@row)) * IF(AND([Flight End]@row - [Flight Start]@row < 365, OR(MONTH([Flight End]@row) = 3, MONTH([Flight Start]@row) = 3)), 0, 31) + IF(AND(OR(MONTH([Flight Start]@row) < 3, YEAR([Flight Start]@row) < YEAR([Flight End]@row)), MONTH([Flight End]@row) = 3), DAY([Flight End]@row), IF(AND(MONTH([Flight Start]@row) = 3, MONTH([Flight End]@row) = 3), DAY([Flight End]@row) - DAY([Flight Start]@row), IF(AND(MONTH([Flight Start]@row) < 3, MONTH([Flight End]@row) > 3), 31, IF(AND(MONTH([Flight Start]@row) = 3, OR(MONTH([Flight End]@row) > 3, YEAR([Flight End]@row) > YEAR([Flight Start]@row))), 31 - DAY([Flight Start]@row), ""))))), "")

And so on. 🙂

Let me know if this works for you or if you're noticing any descrepencies!

Cheers,

Genevieve