How to get a target date for billing date that fall in particular date range?

Copper Contributor
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 Saturday and Sunday comes between then the target date is the next working date.

Can anyone please help me to get this resolved?
17 Replies

@PrakashJha03081998Pj 

Let's say the sale date is in D1. Does the following formula do what you want?

 

=WORKDAY(D1+6,1)

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

@PrakashJha03081998Pj 

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.

@PrakashJha03081998Pj 

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.

@PrakashJha03081998Pj 

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)

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.

@PrakashJha03081998Pj 

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.

There are total three dates involved in this process. Sales date, internal billing date and target date. Internal billing date is working date 4 days after the sales date. And our target date is the next working date on which internal billing for all date in a particular date range is completed.

Please find attached file for your reference. And help me to get a target date formula.

@PrakashJha03081998Pj 

I am really sorry, but I still don't understand.

See for example last date of range 23- 31 is 31 for which internal billing takes place on 4th but as it is Sunday so internal billing took place on 5th.

And therefore our target date got changed from 5th to 6th April.

Target date is based upon the billing date.

@PrakashJha03081998Pj 

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)

It's not working giving #Name? Error.

@PrakashJha03081998Pj

 

????

 

Here is your workbook with the formulas.

Thank you very much for helping me.

I was getting error as I was using excel 2010 version but then I changed formula slightly and used choose function instead of ifs and than it started working.

@PrakashJha03081998Pj 

Ah yes, the IFS function was not in Excel 2010.