Hi - I was hoping someone might help with a formula I need but can’t seem to get right in Monday.
Essentially, I have a timeline column and a numbers column - timeline is how long the task will take to complete(duration) but the numbers column is how many days in the timeline physical work was done(workdays) - i.e. it took 15 days to get done but I was waiting on research for 10 days so it was really only 5 days of work.
This issue I am having is we want to develop annual resource planning based on the number of workdays but some of our tasks span over multiple years. We have a fiscal year of April-March. So if I have a task with a timeline from January 1st to August 1st - I need to remove the time prior to April 1st, then determine what percentage the subtracted days is of that total duration and then subtract that same percentage from the workdays
Example
Timeline 1/1/24-8/1/24 (duration of 214 days)
Workdays 150
We need to subtract 91 days from the timeline(Jan 1 - Mar 31)
That is 42.5% of the timeline
So we need to subtract 42.5% of 150 workdays which is 63.75
Remaining workdays is 86.25
Thank you to anyone who might be able to assist…I am just terrible with formulas in general.
I should add that I have pulled the timeline start and end date into a date column and have added a fiscal year end date start date column to complete this formula
HI @ZakiImtiaz I think this formula should do what I need however the formula column is throwing really weird results. Sometimes it returns the red exclamation point which from what i can tell one of the columns is missing the data needed, so that is expected. For some it is returning a number but it’s the total amount of workdays, not the total minus the % I need. Then for some it’s returning something like an infinity symbol. I’ve been working with Monday support on it for two months but they are unable to tell me why it’s not working. I was wondering if you have any thoughts on how to fix it? I truly appreciate your time on this!
would it make sense to break it down into multiple formula columns - one to count the days before/after the fiscal year and convert to a percentage, then a 2nd one that takes that percentage and subtracts it from the workdays?
