Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I think you could use lastworkdate() within the deadlist-table and adjust with it the date-value and creates a mapping-table from it and then pulling these data with applymap() into your calendar.
- Marcus
I think you could use lastworkdate() within the deadlist-table and adjust with it the date-value and creates a mapping-table from it and then pulling these data with applymap() into your calendar.
- Marcus
I actually wanted Firstworkdate but that was enough to point me in the right direction:
Firstworkdate(Num(PAYMENT_DATE),1,$(vHoliday_GB)) as ACTUAL_PAYMENT_DATE
I'd already got my variable for Holidays to create my masterdates calendar so could easily re-use it here. Thanks!