Calculating Number of Days but Skipping Specific Months

Hello Appsheet Community!

I would like to calculate the number of days between two dates but I want to skip months. Specifically if the month is July or August I want it not to count.

Example: [startDate] = 6/30/2021, [endDate] = 9/1/2021. With these two values, I would want the formula to output 1.

I’m not quite sure how to approach it… any guidance or ideas would be much appreciated. :slight_smile:

  • Andrew

Brainstorming…

Top line is actual timeline, [start] to [finish]; bottom is the timeline to omit, [omit start] to [omit finish].

+--------+
            +----+

Actual does not overlap with omit:

([finish] - [start])

+--------+
      +----+

Actual and omit overlap, omit exceeds actual:

([finish] - [start]) - (min(list([finish], [omit finish])) - [omit start])

+--------+
   +----+

Overlap, omit entirely within actual:

([finish] - [start]) - ([omit finish] - [omit start])

   +----+
+--------+

Overlap, actual entirely within omit:

([finish] - [start]) - (min(list([finish], [omit finish])) - max(list([start], [omit start])))

or just:

"000:00:00"

   +--------+
+----+

Overlap, actual exceeds omit:

([finish] - [start]) - ([omit finish] - max(list([start], [omit start])))
2 Likes

Is there any chance that there could be more than 1 year between the startDate and endDate and lets say if there is July and August occurring twice between these days does it need to be subtracted twice also?

2 Likes

Interesting perspective. I like this thought path.

1 Like

Yes, there is a chance. It’s uncommon but can happen.

Very methodical approach documenting @Steve, thoughtful requirements question @Markus_Malessa

1 Like