Forum Discussion
How to get a target date for billing date that fall in particular date range?
I am working to get a target for a billing date that fall in a given date range. Like for example if sale is made on 1st April than it comes in date range 1-7 so the target date become 8th but if Satu...
Thank you very much. This formula did worked for 3 date ranges but I have four date range 1-7, 8-15, 16-22, and the fourth one is 23 to End of the month. Can you help me for the 4th date range as well.
For 23 - 30 April target date should be 5th of May
Could you attach a sample workbook with examples of sale dates and of the expected target dates?
- I have attached example of sales date and their target dates for feb and March months. Let me know if you need anything else.
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)
