Hi All,
We have the following case - we want to track status of yearly to-do’s (filing date of financial statements of our clients). The deadline depends on another date (which is the fiscal year-end, basically any month end of the year) and is always 12 month after that date, e.g base date: 31.12.2020 - deadline should be 31.12.2021.
The base date can vary from item to item, but is always the last day of a month, e.g. 31.1., 28.02., 30.4. etc.
My current workaround is the following:
-
on a client master data board I configure the fiscal year ending date - for 31.01. of each year I set “1”; for 28.02. of each year I set “2”, etc.
-
in the specific task board for the Year e.g. 2020 (each year is a separate board),I mirror this column and addition I have a column showing the filing year (1 year forward, as the deadline is always 12 month after the base date) and calculate the deadline, using a formula: if(mirror column = 1; “31.01.”&FilingYear). I add the if-formula for every month to cover all potential year-ends.
The result is that I get the following:
- Client master data shows “1” for “31.01.” → Deadline for year 2020 financial statements is (12 month later due) → 31.01.2021
- Client master data shows “2” for “28.02.” → Deadline for year 2020 financial statements is (12 month later due) → 28.02.2021
- Client master data shows “2” for “28.02.” → Deadline for year 2021 financial statements is (12 month later due) → 28.02.2022
Now I want to set this date/formula as a deadline, in order to be able to work with deadline. But as this is a calculated field that looks like a date, it currently does not work.
Do you have any ideas how to solve this?
Summarized:
- deadline for various consecutive years, based on variying month ends (base dates)
- deadline always 12 month later than the task-year (fiscal year for the financial statement); for every task year there is a board
- formula not able to work as deadline
thanks guys
Matthias