Conditional DATETIMEDIFF

[Joanna_Parker]

Hi everyone,

Hitting a bit of a wall with some formulas and wondering if anyone has a more elegant solution to this…

I’m working on a new base that records start and end dates of some work that we do. I then have a datetimediff that calculates the number of days that the record was running (see mocked up example below).

We compile reports annually, and I regularly need to show days of work that only fall within the previous calendar year. At the moment I calculate this by pulling off a CSV, adjusting the start and end dates to only cover the range and do a DATEDIF in Excel.

Can anyone think of an automated/conditional way of only counting days that fall within a certain calendar year? As you can see above, reporting for 2021 would involve projects 2 and 3, and reporting for 2022 would involve projects 1 and 3.

Any thoughts/ideas?

[Joanna_Parker · Accepted solution]

Think I may have found a solution to this that addresses the issue. Leaving some working here in case it comes in handy for anybody else.

The best solution seems to be having a column per year. The below formula is based on the thinking that every project above would fall into one of five categories:

1. Both Start and End Dates fall within the year.
2. Both Start and End Dates fall beyond the year (spanning longer than one year).
3. The Start Date is within the year, and End Date beyond it.
4. The Start Date is before the year, and End Date within it.
5. The Start Date may be later than the year/The End Date may be before the year, so it doesn’t need to be calculated within that year’s column.

Then the formula is:

IF(
AND(YEAR({Start Date})=2022,YEAR({End Date})=2022),
DATETIME_DIFF({End Date},{Start Date},‘days’),
IF(
AND(YEAR({Start Date})<2022,YEAR({End Date})>2022),
DATETIME_DIFF(DATETIME_PARSE(“31 Dec 22”),DATETIME_PARSE(“01 Jan 22”),‘days’),
IF(
AND(YEAR({Start Date})=2022,YEAR({End Date})>2022),
DATETIME_DIFF(DATETIME_PARSE(“31 Dec 22”),{Start Date},‘days’),
IF(
AND(YEAR({Start Date})<2022,YEAR({End Date})=2022),
DATETIME_DIFF({End Date},DATETIME_PARSE(“01 Jan 22”),‘days’),
IF(OR(YEAR({Start Date})>2022,YEAR({End Date})<2022),
“”,
“Error - Please Check”
)
)
)
)
)

This has meant the demo table now looks like this:

It does mean a new column will have to be made each year… but it meets our needs for now.

Feel free to offer any suggestions of improvements/glaring errors :slightly_smiling_face: