Days in specific month between two dates

[Adam_Williams]

I’m trying to create a formula field which calculates the number of days in a specific month between two dates.

For example, the date range 2020-01-27 to 2020-03-12 has:

• 5 days in January
• 29 days in February
• 12 days in March

So in a formula field calculating the “January Days” between the two dates, the value would be “5”.

This has me utterly stumped, can anyone help?

[Jason]

That’s going to be a pretty complex formula @Adam_Williams! As a start, here’s a formula that you could use to identify which months of the year a date range falls within.

DATETIME_FORMAT(
   {Start Date}, 'MMM' & ", "
) 

& 

MID(
   'Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec', 
   FIND(
      DATETIME_FORMAT({Start Date}, 'MMM'), 'Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec'
   )+5, 
   3+4*(DATETIME_DIFF({End Date}, {Start Date}, 'months'))
)

This is adapted from a similar solution in this post.

After identifying the months, you’ll need a separate list of nested IF statements to conditionally subtract the difference in days for each month within the date range, accounting for the partial months for the start and end dates.

Let’s see if some other community members have a good solution for that portion!

[Justin_Barrett]

While the excellent formula from @Jason gives you the big picture, what I got from your initial request was that you would have separate fields for each month; e.g. a {January Days} field that only tracks how many days from that range are in January, {February Days} for February, etc. That makes it slightly easier, but it also brings up a question: will you have any date ranges that span more than one year? For example, a range that begins on 12/1/2020 and ends 4/2/2021? If so, will that change how you want the calculations displayed?

I have to move on to some other work, so I don’t have time to dive deep into this at the moment, but I might play with it later on. I’ve got some ideas percolating.

[Adam_Williams]

Thank you both for your input. @Justin_Barrett you’re exactly right, ultimately I will have 12 columns, one for each month of the year, each identifying the “january days”, “february days” etc.

The actual purpose of this is a KPI Dashboard for a holiday let property, to gauge booking performance over the course of a year, broken down monthly.

You make a really good point about date ranges spanning more than one year, because people could easily book the property for, say, December 24th right through till January 3rd. If we just have a “January” column, those figures then potentially get skewed by having results from 2 Januarys!

[Justin_Barrett]

I have something working. One bonus is that it’s very specific about date ranges, so the {Jan} field (I shortened the names so I could get all twelve months on the screen at once) will only work for January 2020. However, that also means that once you need to start tracking 2021, you’ll need to duplicate the 2020 fields and tweak the formulas for next year.

Conceptually it’s pretty simple—clamp the dates to only those from the range that fall within the month using MIN() and MAX(), then find the difference—but to execute it I had to do a lot more conversions than I anticipated. (It’s a little annoying that MIN() and MAX() correctly operate on dates in rollup field aggregation formulas, but not in formula fields, so I ended up converting all dates to/from their Unix timestamp equivalents to pull it off.)

Here’s the formula for January. The rest just have changes to the dates and to the final MONTH() comparison.

IF(
    AND({Start Date}, {End Date}),
    IF(
        AND(
            {Start Date} < DATETIME_PARSE("2/1/2020"),
            {End Date} >= DATETIME_PARSE("1/1/2020")
        ),
        DATETIME_DIFF(
            DATETIME_PARSE(MIN(VALUE(DATETIME_FORMAT({End Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("2/1/2020"), "X"))), "X"),
            DATETIME_PARSE(MAX(VALUE(DATETIME_FORMAT({Start Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("1/1/2020"), "X"))), "X"),
            "days"
        ) + (MONTH({End Date}) = 1),
        0
    )
)

Rather than make you mess with the formulas manually, I made a base that you can copy from.

Date Span by Months - Airtable

[Adam_Williams]

This is absolutely perfect @Justin_Barrett and does exactly what I need. Thank you!