Need help with formula (If/then complex forumula)

[Eurus53]

Hey all!

I am unfortunately stumped by an excel task, so I've come to look for some help! I've heard amazing things about this forum, so I figured it was worth a post

Here's what I'm trying to do: (basically trying to generate what date a certain report is due by, based on the date the action occurred)

At my job, we have to file regular reports for actions we've completed during certain reporting periods. So I've come up with this dataset containing the reporting schedule:

Report Period Start Report Period End Due date for report period
5/1/2016 5/31/2016 6/10/2016
6/1/2016 7/11/2016 7/12/2016
7/12/2016 7/25/2016 7/26/2016
7/26/2016 8/31/2016 9/12/2016
9/1/2016 10/17/2016 10/18/2016
10/18/2016 10/31/2016 11/1/2016
11/1/2016 11/30/2016 12/12/2016

Now, in a separate part of the excel spreadsheet, I have a list of all the actions taken, along with the date they occurred.

Action # Action Date
Action 1 2/26/2016
Action 2 4/3/2016
Action 3 5/18/2016
Action 4 10/16/2016

Here's what I would like to generate (Action Report Due Date) based on the information I have above working in tandem using some sort of a formula:

Action # Action Date Action Report Due Date
Action 1 2/26/2016 3/10/2016
Action 2 4/3/2016 5/10/2016
Action 3 5/18/2016 6/10/2016
Action 4 10/16/2016 10/18/2016

Can anyone tell me how I would be able to accomplish this??

Please let me know, I am happy to answer any questions!

Best,

Eurus53

[FlameRetired]

Eurus53 welcome to the forum.

We'll need some context. That means uploading a small representative sample Excel spreadsheet file (and please not pics or screen shots ... we can't edit or test formulas there).

If you are not familiar with how to do this:

To attach a file to your post,

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”
• be sure to desensitize the data

The file name will appear at the bottom of your reply.

PS Please take the time to update your profile ... Excel versions. Members tailor solutions with profile details in mind.

[Eurus53]

FlameRetired --

Thank you so much for the advice! As a newbie, I appreciate it.

I just went and uploaded the file, hopefully it is helpful.

I am using Excel 2010 version.

[FDibbins]

I cannot see where you get those dates from in the 3rd table, they do not exist anywhere else?
table 1

	A	B	C
1	Report Period	Start Report Period	End Due date for report period
2	5/1/2016	5/31/2016	6/10/2016
3	6/1/2016	7/11/2016	7/12/2016
4	7/12/2016	7/25/2016	7/26/2016
5	7/26/2016	8/31/2016	9/12/2016
6	9/1/2016	10/17/2016	10/18/2016
7	10/18/2016	10/31/2016	11/1/2016
8	11/1/2016	11/30/2016	12/12/2016

table 2

	E	F
1	Action #	Action Date
2	Action 1	2/26/2016
3	Action 2	4/3/2016
4	Action 3	5/18/2016
5	Action 4	10/16/2016

table 3

	H	I	J
1	Action #	Action Date	Action Report Due Date
2	Action 1	2/26/2016	3/10/2016
3	Action 2	4/3/2016	5/10/2016
4	Action 3	5/18/2016	6/10/2016
5	Action 4	10/16/2016	10/18/2016

[FDibbins]

OK thanks for the file,makes it much easier. Try this, copied down...
=INDEX($C$2:$C$24,MATCH(B28,$C$2:$C$24)+1)

[FlameRetired]

Yup. I think we need a workbook.

[Eurus53]

I cannot see where you get those dates from in the 3rd table, they do not exist anywhere else?
table 1

	A	B	C
1	Report Period	Start Report Period	End Due date for report period
2	5/1/2016	5/31/2016	6/10/2016
3	6/1/2016	7/11/2016	7/12/2016
4	7/12/2016	7/25/2016	7/26/2016
5	7/26/2016	8/31/2016	9/12/2016
6	9/1/2016	10/17/2016	10/18/2016
7	10/18/2016	10/31/2016	11/1/2016
8	11/1/2016	11/30/2016	12/12/2016

table 2

	E	F
1	Action #	Action Date
2	Action 1	2/26/2016
3	Action 2	4/3/2016
4	Action 3	5/18/2016
5	Action 4	10/16/2016

table 3

	H	I	J
1	Action #	Action Date	Action Report Due Date
2	Action 1	2/26/2016	3/10/2016
3	Action 2	4/3/2016	5/10/2016
4	Action 3	5/18/2016	6/10/2016
5	Action 4	10/16/2016	10/18/2016

FDibbins --

Thank you for your help! I just put those values there because those are the values that the formula should generate.

The first set of data in the excel I attached is how I generated the report due date. I.E. if an action took place between 1/1/16 and 1/31/16 the due date for the report on the action is 2/10/16.

[FDibbins]

I replied again after I posted those 3 tables, based on the file you provided

[Eurus53]

OK thanks for the file,makes it much easier. Try this, copied down...
=INDEX($C$2:$C$24,MATCH(B28,$C$2:$C$24)+1)

FDibbins --

This formula is brilliant. I still don't entirely understand how youv'e done this, but it works flawlessly. I'm going to try to adapt it to my big spreadsheed now. You are amazing -- thank you.

[FDibbins]

It works on your sample data, but test it carefully on your live data.

The MATCH does the heavy lifting, , but it is returning the cell 1 before the dates you gave, so I added +1 to get it to use the next row/date down

[Eurus53]

Well, the formula certainly works very well, but not flawlessly, for reasons I don't understand.

Sometimes the forumula is not returning the correct results, even using the same table I posted up before, any idea? Here is an example of some of the incorrect results returned.

Is there maybe a character that I am missing in the formula?

Here is what it gives me, with the third column being the errors. Anyone understand what is going on here?

expenditure_date Due Date
2/5/2016 2/10/2016 Should be 3/10/2016
2/23/2016 3/10/2016 Correct.
2/27/2016 3/10/2016 Correct.
3/7/2016 3/10/2016 Should be 4/11/2016
3/22/2016 4/11/2016 Correct.
3/25/2016 4/11/2016 Correct.
4/3/2016 4/11/2016 Should be 5/10/2016
4/6/2016 4/11/2016 Should be 5/10/2016
4/6/2016 4/11/2016 Should be 5/10/2016
4/15/2016 5/11/2016 Should be 5/10/2016
5/24/2016 6/10/2016 Correct.
5/25/2016 6/10/2016 Correct.

[Eurus53]

and here is the attached version of excel. You have to scroll all the way down to "Action 36" to see what I'm talking about. Again, it works for most everything, but sometimes it is off, and I cannot understand why.

[davsth]

I think you have not given us some logic, is there a minimum numbers of days before the due date can happen most of the differences are within 10 days?

Perhaps an amendment like =INDEX($C$2:$C$24,MATCH(B28+x,$C$2:$C$24)+1) where x is this minimum number of days that you have not told us about!!

[Eurus53]

Hey Davsth --

Unfortunately, no there is no real logic related to the due dates! In reality, the dates are somewhat arbitrary, with them actually changing from year to year based on the difference dates of election day. That's why I had to go through an manually enter them in the table with ranges and a due date.

[davsth]

but the formula finds the date after the action date. the dates you say are wrong are when the date the formula returns is a few days after the action date. So it seems that is there a minimum number of days before the action date is applied. Eg if the due date was the day after the action date, would this be the date you would use?

[FlameRetired]

If I have the concept correctly here are two alternatives as I understand it.

They both work at my end and return 7/12/2016 for Action #36.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Eurus53]

but the formula finds the date after the action date. the dates you say are wrong are when the date the formula returns is a few days after the action date. So it seems that is there a minimum number of days before the action date is applied. Eg if the due date was the day after the action date, would this be the date you would use?

Unfortunately there is no minimum amount of days before the action date is applied. At least, not on any uniform basis. I wish I had a better answer.

I guess what I'm looking for is a formula that will first a) take the date of the action, and b) compare that to my table to see when reports for the time period are due. There must be some way to achieve this? The formula FDibbins came up with gets me nearly all the way there, but it doesn't work uniformly 100% of the time. Maybe there is a small tweak I can make?

[FDibbins]

How do you get 7/12?
I thought the process was to find the 1st date after the action date?
Action date = 6/8
closest "before" date would be 5/11
date after that would be 6/10

[FlameRetired]

I.E. if an action took place between 1/1/16 and 1/31/16 the due date for the report on the action is 2/10/16.

I have been interpreting that way all along. Did you try either of the formulas in post #16 above?

They seem to work at my end. Can you tell me what they are not doing right?

With source data:

Row\Col	A	B	C
1	Report Period Start	Report Period End	Due date for report period
2	7/14/2015	7/27/2015	7/28/2015
3	7/28/2015	8/31/2015	9/10/2015
4	9/1/2015	10/12/2015	10/13/2015
5	10/13/2015	10/26/2015	10/27/2015
6	10/27/2015	11/30/2015	12/10/2015
7	12/1/2015	12/31/2015	1/11/2016
8	1/1/2016	1/31/2016	2/10/2016
9	2/1/2016	2/29/2016	3/10/2016

[Eurus53]

I cannot see where you get those dates from in the 3rd table, they do not exist anywhere else?
table 1

	A	B	C
1	Report Period	Start Report Period	End Due date for report period
2	5/1/2016	5/31/2016	6/10/2016
3	6/1/2016	7/11/2016	7/12/2016
4	7/12/2016	7/25/2016	7/26/2016
5	7/26/2016	8/31/2016	9/12/2016
6	9/1/2016	10/17/2016	10/18/2016
7	10/18/2016	10/31/2016	11/1/2016
8	11/1/2016	11/30/2016	12/12/2016

table 2

	E	F
1	Action #	Action Date
2	Action 1	2/26/2016
3	Action 2	4/3/2016
4	Action 3	5/18/2016
5	Action 4	10/16/2016

table 3

	H	I	J
1	Action #	Action Date	Action Report Due Date
2	Action 1	2/26/2016	3/10/2016
3	Action 2	4/3/2016	5/10/2016
4	Action 3	5/18/2016	6/10/2016
5	Action 4	10/16/2016	10/18/2016

FDibbins --

Thank you for bearing with me on this one.

The 7/12/2016 due date for the action that occured on 6/8/2016 is because the report (also known as a C4 report) for that time period (which is 6/1/2016 to 7/11/2016 -- also denoted in my earlier table) is due on 7/12/2016. Unfortunately, it is a more or less arbitrary time schedule specified by law.

Attached I have attached the schedule for the ranges in question. You can see on the far left hand corner the required report submission date and on the far right hand column labeled "C-4 report period" coveres the data range. Hopefully this clears things up slightly.

[Eurus53]

I have been interpreting that way all along. Did you try either of the formulas in post #16 above?

They seem to work at my end. Can you tell me what they are not doing right?

With source data:

Row\Col	A	B	C
1	Report Period Start	Report Period End	Due date for report period
2	7/14/2015	7/27/2015	7/28/2015
3	7/28/2015	8/31/2015	9/10/2015
4	9/1/2015	10/12/2015	10/13/2015
5	10/13/2015	10/26/2015	10/27/2015
6	10/27/2015	11/30/2015	12/10/2015
7	12/1/2015	12/31/2015	1/11/2016
8	1/1/2016	1/31/2016	2/10/2016
9	2/1/2016	2/29/2016	3/10/2016

FlameRetired --

I'm embarresed, I just saw your response above! I hadn't realized there was a page 2 on this thread until the most recent correspondence. Let me try applying this to my large spreadsheet now and seeing if it worked. If it worked for you though, I'm sure it will work for me!

[Eurus53]

FlameRetired --

I'm embarresed, I just saw your response above! I hadn't realized there was a page 2 on this thread until the most recent correspondence. Let me try applying this to my large spreadsheet now and seeing if it worked. If it worked for you though, I'm sure it will work for me!

FlameRetired --

Thank you so much for your help, this appears to be working perfectly now!

[FlameRetired]

Glad to hear it. You are welcome, and thank you for the feedback.