Days in specific month between two dates

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!

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. :slightly_smiling_face:

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!

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

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

Hi guys, 

Related to the topic above, I would like to check if its possible to apply the same solution but to calculate only the workdays, I have tried adjusting the formula but I am not able to come up with the right function/formula. 

Can anyone help? 

Thank you.

@Jason11 

Hello ! I'm interested as well to calculate only the workdays