CODEWITHSUNDEEP
excelexcel-formula
Peter Parker
Peter Parker

Reputation: 5

Excel subtract X days from date and then find last specific workday date

here's my problem:

We have an Excel sheet at work in order to manage various tasks all set on individually specified timelines - we're talking hundreds of tasks per month with dates that are currently all being maintained manually.

Let's say we have Task A and that task has a Due Date. That Task is split into several subtasks, all with their individual due dates. Date 1 would then always be Due Date - 10 days, Date 2 would be Due Date - 20 days, Date 3 would be Due Date - 17 days and so on. This then creates a neat timeline of when everything needs to be done.

That in itself would be easy enough, problem is that all of these subtasks have to be done on a specific workday as well. Meaning that subtask 1 would not only have to be done Due Date - 10 days, but it would also have to fall on a Monday - if for whatever reason Due Date - 10 would happen to fall on like a Wednesday, it would have to subtract another 2 days. And then the real problem is that Date 2 and 3 each have to fall on different workdays and Task B has an entirely different schedule again.

Now, the first thing that came to my mind was attempting to just nest a couple IFs - and I've even managed to come up with a working formula for that. Problem here is that it's so stupidly long and (thanks Excel) utterly unreadable - if for some reason someone else had to change something about it or troubleshoot the entire file 1-2 years from now, they'd probably have to spend at least an hour reconstruct how the hell any of it actually worked. Which doesn't sound particularly appealing to us.

Here's a screenshot to help illustrating the situation: Image And here's a draft of the current formula that I'm really not happy with, despite it somewhat doing the job: =IF(AND(WEEKDAY(K2-VLOOKUP(B2,Table1,3,FALSE),11)>5,VLOOKUP(B2,Table1,5,FALSE)=0),(K2-VLOOKUP(B2,Table1,3,FALSE))-(WEEKDAY(K2-VLOOKUP(B2,Table1,3,FALSE),11)-5), IF(VLOOKUP(B2,Table1,5,FALSE)=0,K2-VLOOKUP(B2,Table1,3,FALSE), IF(WEEKDAY((K2-VLOOKUP(B2,Table1,3,FALSE)),11)=0,K2-VLOOKUP(B2,Table1,3,FALSE), (VLOOKUP(B2,Table1,5,FALSE)-WEEKDAY(K2-VLOOKUP(B2,Table1,3,FALSE),11))+K2-VLOOKUP(B2,Table1,3,FALSE))))

My question is now: Does anyone have an idea how to solve this in a less confusing and unclear manner? I was trying to get something done using =CHOOSE() but ultimately ended up with the same problem of eventually having to resort to 7 IFs and dozens of LOOKUPs, making the final formula just as long. I wouldn't be disinclined towards some kind of helper table that asigns the last 7 days and their workdays to every single day of the year... but I don't think tossing another 20,000 calculated cells into a file that already has tens of thousands other calculated cells would really be a serviceable alternative... or make the situation any less obscure at all tbh. So, anyone any idea how to go about this? Or is there really no realistic alternative than to use a bunch of IFs?

Edit: Forgot to mention that 1 special case: There's also the situation when a date doesn't have to fall on a specific workday - in which case it's simply due date - X days. The problem here is that in those cases the dates could fall on a weekend, so the formula would have to move these dates to the previous Friday as well.

Upvotes: 0

Views: 558

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Assume you have some date and you want to "back up" ten days and then to the preceding Monday, unless the resultant date is a Monday. The general formula would be something like:

=A2-10+1-WEEKDAY(A2-10-DOW)

Where DOW translates into

Sunday=0
Monday=1
Tuesday=2
...

You should be able to modify your formulas to use this algorithm for day of the week.

If the two tables are named thisTable and keyTable, the following 365 formula is one way of implementing:

If your tables are as below, you can enter the formula in B2 and fill down and across. The references should self-adjust and return the proper dates.

Note that in keyTable, I enter the day of the week DOW as defined above, and not the textual date.

=LET(dys,VLOOKUP(thisTable[@[Task]:[Task]],KeyTable,COLUMNS($A:A)*2,FALSE),
     dow,VLOOKUP(thisTable[@[Task]:{Task]],KeyTable,COLUMNS($A:A)*2+1,FALSE),
      due,thisTable[@[DueDate]:[DueDate]],
IF(dow="flexible",WORKDAY(due-dys+1,-1),due-dys+1-WEEKDAY(due-dys-dow)))

enter image description here

If you want to have the result be the closest workday, instead of the preceding workday, then you need to add two clauses to the LET function

  • Calculate the subsequent workday date
  • Then use an IF to return the closest one to the original target

eg:

=LET(dys,VLOOKUP(thisTable[@[Task]:[Task]],KeyTable,COLUMNS($A:A)*2,FALSE),
     dow,VLOOKUP(thisTable[@[Task]:[Task]],KeyTable,COLUMNS($A:A)*2+1,FALSE),
     due,thisTable[@[DueDate]:[DueDate]],
     dayPrev,IF(dow="flexible",WORKDAY(due-dys+1,-1),due-dys+1-WEEKDAY(due-dys-dow)),
     daySubseq,IF(dow="flexible",WORKDAY(due-dys+1,-1),due-dys+7-WEEKDAY(due-dys-1-dow)),
  IF((daySubseq-due+dys)>3,dayPrev,daySubseq))

enter image description here

Upvotes: 1

Related Questions