Date calculation on a field that is being brought in by reference formula

Brianna Mendez

Hi there,

I am working on a sheet where I am trying to calculate a date 30 days from the "M1 Attendance Date" into the "M1 Exam Due Date".

The M1 attendance date is being fed into this sheet via a "=JOIN(COLLECT" formula from another sheet, as follows "=JOIN(COLLECT({M1 Date}, {Name}, [Combined First and Last Name]@row))", this formula works just fine.

When I try to do a simple "=[M1 Attendance Date]@row+30", it just adds the number 30 to the end of the date from the previous column (see image below).

The eventual goal is once the due date gets calculated is to have the sheet calculate the difference between the exam due date and the "M1 exam completion date" to identify if the person was past due on their exam, and if so, by how many days.

The "M1 exam completion date is being fed in from a different sheet as well with the formula, "=JOIN(COLLECT({Exam Completion Date}, {Combined Name}, [Combined First and Last Name]@row, {Course Title}, "Academy M1 Exam"))", which is obviously working at the present moment.

Any help with figuring out the second formula to get a number of days to populate in the duration column would be appreciated as well since there are many date fields on the sheet.

Additional info: the columns used for the 2 different feed sheets for the auto-populated dates (attendance date and exam completion date) are formatted as "Date" columns, so they would create the date shown. The columns they are populating into on the sheet above are also formatted as date columns.

Thank you in advance!

Brian Wilson DC

@Brianna Mendez Hey, Brianna! For some reason the second half of your post wasn't visible until after I posted my response.

I see that you mentioned they are formatted as date columns. I'm able to produce your desired result below. Do you have any date formatting tied to those columns possibly affecting the outcome?

As you can see below a simple column + 30 yields the date you are looking for and should work for your sheet, assuming the column types are setup correctly.

I hope this helps!

EDIT: I know your destination sheet you've mentioned is setup as date columns. Are the source sheets setup the same way with the same formatting [date and date format type (i.e., MM/DD/YY)]?

Regards,

Brian

Brianna Mendez

Hi @Brian Wilson DC !

Thank you for responding. So the columns are all formatted the same (MM/DD/YY) by default, but I did go back and double check.

The original attendance column is being fed into this sheet with this formula.

Do I need to change something in the formatting of this column to get it to recognize that the answer is a date to add the 30 to for the Due Date column? Because I feel like it is some how just taking this formula and adding the number 30 on the end because this is a formula for the whole column, even though it is formatted as a date.

Brian Wilson DC

@Brianna Mendez Okay! I was able to recreate a two sheet setup and your current problem. If you change your search to an INDEX/MATCH function it will work and pull the date in as a date that will allow you to add your +30. So I'm using this formula and it pulls in the correct date. And then in my Due Date Column simply add +30 changed it appropriately:

=INDEX({M1 Date}, MATCH(Name@row, {Name}))

{M1 Date} = The date column on your source sheet for the M1 Attendance Date

[Name]@row = the column on your destination sheet where you have your candidate's name you want to match to the source sheet.

{Name} = The name column on your source sheet, which is the same sheet you are pulling the M1 Date from.

I hope this helps!

-Brian

Brianna Mendez

@Brian Wilson DC , thank you, that worked!

Now my new problem is to take the date from the due date column and calculate the difference between that and the Exam Completion Date column.

Below is the screenshot for the completion date formula. It has 2 different criterion it is searching for to bring back the date requested.

Any ideas how to get the differences in the dates to calculate under the "M1 Duration" column?

I tried using the "duration" setting in the columns, but it is not capturing the dates in question because they are being pulled in by formulas.

Any and all suggestions would be much appreciated!

Brian Wilson DC

@Brianna Mendez We are going to address that the same way. This time with an INDEX, MATCH, MATCH statement. The first Index and match are similar to how we calculated our exam date. Then we add a second match condition to look for the exam name you need and reference that to the course title column on your source sheet.

=INDEX({Exam Complete Date}, MATCH(Name@row, {Name}), MATCH("Academy M1 Exam", {Course Title}))

{Exam Complete Date| = Source sheet exam completion date

{Name} = Source sheet candidate's name

{Course Title} = Source sheet course title column that you'd like to match the specific course listed in the parenthetical statement to. In this case, "Academy M1 Exam".

EDIT: To answer the second part of your question that I completely forgot about ;) After changing your Exam Completion Date to the formula above, it will now allow you to do a simple subtraction statement between your Due Date and Completion Date Columns. You will just need to put them in the correct order to display a negative or positive value as you see fit!

I hope this helps!

Brianna Mendez

@Brian Wilson DC ,

Thank you. That worked. Now I am duplicating that calculation two more times in this sheet, as there are 3 different exam dates I am pulling over for each student into different columns. The exam titles are "Academy M2 Exam" and "Academy M3" exam. When I tried to replicate the above formula and change the exam titles in the formula, it kicks back "#INVALID VALUE".

Any ideas?

Here is what I am entering:

=INDEX({Exam Completion Date}, MATCH([Combined First and Last Name]@row, {Combined Name}), MATCH("Academy M2 Exam", {Course Title}))

Brian Wilson DC

@Brianna Mendez Usually invalid value means you are trying to put a date value into a non-date column, or something similar. Is the dropdown column you've entered this new formula into setup as date column?

Brianna Mendez

Yes they are.

Brian Wilson DC

@Brianna Mendez First, don't use copy and paste to use that formula in the other two cells. If you do that it is going to pull the cell references, etc. Then when you go to change them it might change it for the other times it is used in the sheet.

Try typing in the formula you want to work and make sure that you are using the correct exam date reference. You should have 3 different equations with three different references. If you used the same formula, but changed only the Exam Type from M1, to M2, to M3, then it's not going to work correctly.

I.E, it should look something more like below (highlighting to note differences):

=INDEX({Exam Complete Date}, MATCH(Name@row, {Name}), MATCH("Academy M1 Exam", {Course Title}))

=INDEX({M2 Exam Complete Date}, MATCH(Name@row, {Name}), MATCH("Academy M2 Exam", {Course Title}))

=INDEX({M3 Exam Complete Date}, MATCH(Name@row, {Name}), MATCH("Academy M3 Exam", {Course Title}))

I hope that resolves what is going on.

I have to step out for a few hours, but I will check back over the weekend if you can't move forward with this tonight!

Brianna Mendez

Hi @Brian Wilson DC ,

So the main report that this is coming from has all 3 exams listed, as it is coming from our LMS. All "Exam completion dates" are filed under the same column, no matter the exam title. There is no field to match the index field you highlighted at the beginning of your index formulas.

Would it make more sense to have the exams split into 3 different sheets?

Brian Wilson DC

@Brianna Mendez Nope, what you have will work! So you basically have the person's name listed on three different rows then? Tracking their progress through each exam step?

Brianna Mendez

Hi @Brian Wilson DC , you are correct, the name is listed on three different rows.