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...
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.
Ah yes, the IFS function was not in Excel 2010.
- 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. - It's not working giving #Name? Error.
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)
- 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. I am really sorry, but I still don't understand.
- 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. 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.