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?
Accepted Solutions
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:
- Both Start and End Dates fall within the year.
- Both Start and End Dates fall beyond the year (spanning longer than one year).
- The Start Date is within the year, and End Date beyond it.
- The Start Date is before the year, and End Date within it.
- 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:
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:
- Both Start and End Dates fall within the year.
- Both Start and End Dates fall beyond the year (spanning longer than one year).
- The Start Date is within the year, and End Date beyond it.
- The Start Date is before the year, and End Date within it.
- 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:
