Forum Discussion
How to get a target date for billing date that fall in particular date range?
Could you attach a sample workbook with examples of sale dates and of the expected target dates?
If the 6th of April should have been the 5th, use
=WORKDAY(DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>22),IFS(DAY(A2)<8,11,DAY(A2)<16,19,DAY(A2)<23,26,DAY(A2)>22,4)),1)
or
=WORKDAY(DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>22),LOOKUP(DAY(A2),{1,8,16,23},{11,19,26,4})),1)
If the 5th of April was a public holiday, create a list of public holidays and name this range Holidays. Change the formula to
=WORKDAY(DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>22),IFS(DAY(A2)<8,11,DAY(A2)<16,19,DAY(A2)<23,26,DAY(A2)>22,4)),1,Holidays)
or
=WORKDAY(DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>22),LOOKUP(DAY(A2),{1,8,16,23},{11,19,26,4})),1,Holidays)
You have the 6th of April for sales dates from the 23rd of March to the 31st of March.
Why a Tuesday?
I don't understand the logic behind it.
- The logic is after 4 days internal billing takes place for a particular date range. Like for example internal billing date will be 11th for date range 1-7 and the next working date becomes our target date which is 12th. Similarly for date range 23-31 internal billing date was 4th but as it was a Sunday it becomes 5th and therefore our target date changed to 6th of April.
That doesn't make sense to me. The first working day after Sunday the 4th of April is Monday the 5th. Why add yet another day? Tuesday the 6th is 2 working days after Sunday the 4th.