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

Occasional Contributor

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

17 Replies

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

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

=WORKDAY(D1+6,1)

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

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.

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

For 23 - 30 April target date should be 5th of May

# Re: 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?

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

I have attached example of sales date and their target dates for feb and March months. Let me know if you need anything else.

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

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.

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

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)

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

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.

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

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.

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

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.

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

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

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

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.

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

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)

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

It's not working giving #Name? Error.

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

@PrakashJha03081998Pj

????

Here is your workbook with the formulas.

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

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.

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

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