I've got a master calendar that includes weekends, public holidays and other special days where no-one will be in the office. I've also got a list of deadlines. If the deadlines is on a non-working day, I need to find the previous working day. n.b. I need to do this in the script (not visually in a chart).
As you can see the Working Day No is sequential. The flag for Working day is 1 and 0 being non-working day:
I've got this list of Deadlines. The ones in yellow are actually non-working days, so the actual deadline is the previous working day. Therefore I needs a new column/field adding on to this table to show the ACTUAL_PAYMENT_DATE that checks if the PAYMENT_DATE is a non-working day, and if so finds the previous working day:
Can anyone suggest how I do this? I suspect it's quite simple but I just don't know the answer.
n.b. The attached datemaster.xlsx is actually a QVD but the forum would not allow me to upload so I converted it to xlsx.