Skip to main content

Hi,

I have a formula that currently works 90% of the time which finds the number of days between a date range for a given month. The issue is, when the end date, ends in the month I am looking for, example below Oct the formula is correct (row 1, 3 and 4 in the image below). However, when the end date includes BUT does not end in the given month (example: row 2 or 5 in the image below) I get 30 days. I want it to show as 31 days because the date range is inclusive of Oct 2024. Any suggestions? 

Here is the formula,

IF(
AND({Start Date}, {End Date}),
IF(
AND(
{Start Date} <= DATETIME_PARSE("10/31/2024"),
{End Date} >= DATETIME_PARSE("10/01/2024")
),
DATETIME_DIFF(
DATETIME_PARSE(MIN(VALUE(DATETIME_FORMAT({End Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/31/2024"), "X"))), "X"),
DATETIME_PARSE(MAX(VALUE(DATETIME_FORMAT({Start Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/01/2024"), "X"))), "X"),
"days"
) + (MONTH({End Date}) = 10),
0
)
)

 

Full disclosure - since I don't have the context for what you're trying to achieve, and I can't quite get my head around the formula logic, I just pasted your whole question into Gemini. So while I tested the output and see that it's working, I can't vouch for the approach 😅.  In fact, I suspect the entire problem can be approached in a better way, but here's Gemini's response (everything in italics):

The issue lies in how your formula calculates the difference when the end date doesn't end in October. It seems to be prematurely cutting off the calculation at the end of September.

Here's a revised formula that should accurately calculate the days, inclusive of October, even when the end date falls in November:

IF(

AND({Start Date}, {End Date}),

IF(

AND(

{Start Date} <= DATETIME_PARSE("10/31/2024"),

{End Date} >= DATETIME_PARSE("10/01/2024")

),

DATETIME_DIFF(

DATETIME_PARSE(MIN(VALUE(DATETIME_FORMAT({End Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/31/2024"), "X"))), "X"),

DATETIME_PARSE(MAX(VALUE(DATETIME_FORMAT({Start Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/01/2024"), "X"))), "X"),

"days"

) + 1, 

0

)

)

Explanation of the change:

  • + 1: Instead of + (MONTH({End Date}) = 10), we simply add 1 to the DATETIME_DIFF result. This ensures that the last day of October is always included in the count when the date range overlaps with October.

How it works:

  1. IF(AND({Start Date}, {End Date})): Checks if both start and end dates exist.
  2. IF(AND({Start Date} <= DATETIME_PARSE("10/31/2024"), {End Date} >= DATETIME_PARSE("10/01/2024"))): Checks if the date range overlaps with October.
  3. DATETIME_DIFF(...): Calculates the difference in days between the later of the start date or October 1st and the earlier of the end date or October 31st.
  4. + 1: Adds 1 to include the last day of the period in the count.

This revised formula should give you the correct number of days in October for all your date ranges.

 

Here's how it looks when I tested it:

 

@Matt_Jastremski this is perfect! Why didn't I think of using Gemini? Thank you so much!!

@Matt_Jastremski this is perfect! Why didn't I think of using Gemini? Thank you so much!!


LLMs can be super helpful in puzzling through tricky problems, but just keep in mind that while you might be solving the more immediate tactical problem, you could be painting yourself into a corner strategically! 

I've seen a lot of Airtable formulas generated by by AI that technically work, but are needlessly complex, nearly impossible to decipher, or involve myopic design decisions. So feel free to reach out if you have any more issues!

Reply


Terms & Conditions