![Joanna_Parker Joanna_Parker](https://community.airtable.com/legacyfs/online/avatars/3X/8/0/80b719fc208cb6c5c032657158762aaf66c39d9b.jpeg)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 22, 2022 01:54 AM
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?
Solved! Go to Solution.
Accepted Solutions
![Joanna_Parker Joanna_Parker](https://community.airtable.com/legacyfs/online/avatars/3X/8/0/80b719fc208cb6c5c032657158762aaf66c39d9b.jpeg)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 23, 2022 08:36 AM
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:
![Joanna_Parker Joanna_Parker](https://community.airtable.com/legacyfs/online/avatars/3X/8/0/80b719fc208cb6c5c032657158762aaf66c39d9b.jpeg)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 23, 2022 08:36 AM
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:
![](/skins/images/FE00829FDD2AE889FAB731D8F02A8942/responsive_peak/images/icon_anonymous_message.png)