Apr 19 2021 11:50 AM
Apr 19 2021 12:13 PM
Let's say the sale date is in D1. Does the following formula do what you want?
=WORKDAY(D1+6,1)
Apr 19 2021 12:21 PM
Apr 19 2021 12:22 PM
Apr 19 2021 12:26 PM
Could you attach a sample workbook with examples of sale dates and of the expected target dates?
Apr 19 2021 02:35 PM
Apr 19 2021 02:48 PM
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.
Apr 19 2021 03:18 PM - edited Apr 19 2021 03:22 PM
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)
Apr 19 2021 11:17 PM
Apr 20 2021 01:22 AM
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.
Apr 20 2021 06:11 AM
Apr 20 2021 06:47 AM
I am really sorry, but I still don't understand.
Apr 20 2021 07:06 AM
Apr 20 2021 07:24 AM
Try this:
=WORKDAY(WORKDAY(DATE(YEAR(A2),MONTH(A2),IFS(DAY(A2)<=7,7,DAY(A2)<=15,15,DAY(A2)<=22,22,DAY(A2)>22,DAY(EOMONTH(A2,0))))+3,1),1)
Apr 20 2021 12:15 PM
Apr 20 2021 12:24 PM
Apr 21 2021 11:00 AM
Apr 21 2021 11:34 AM
Ah yes, the IFS function was not in Excel 2010.