Calculate Date Due in one table based on hire date / relative due date in another table

[Ilyssa_Russ]

Hey team,

Putting together a Coda doc for an Onboarding tracker.

I have a series of “Running Tasks” with relative due dates. I want to use the New Hire’s Start Date in one table, have it subtract days from that date based on my Running Task list, and then have it spit out the date the task should be done based on that calculation. I hope that makes sense. I’m not thinking this is possible based on one attribute being a Date and the other being a number (like -30, -20)

[Piet_Strydom]

HI Ilyssa,

What you are trying to do is very possible.

You need a column type of Date → Duration;

With the format of the type of duration:
**

And finally, you just add the duration to the date:

P

[Ilyssa_Russ]

@Piet_Strydom I am shaking my head at how silly I’ve been. Thank you so much.

[Ilyssa_Russ]

@Piet_Strydom Wait. So I can get it to work in one single Table… but I can’t get it to work if I’m referencing other tables.

[joost_mineur]

A date is just a number. To change a number to a date, add something like “.todate()” to your formula.
In the screenprint you don’t calculate a singel value, but you are calculating on 2 lists (all the start dates and all the duration).
What you want to do can be done, but is requires a different approach - and I am not really sure what you are trying to accomplish.
If you share a dummy doc with this community, you will get better answers that are really going to help you. Make sure you add some text in your doc so that it is clear which values your are trying to combine of are intended to be used in your calculation.

[Piet_Strydom]

Hi Ilyssa

In the formula below, you are adding the list of Start Dates in Table New Hires Overview, to the list of Durations from table Test Table Dates. That is why the result is a list of values - 45490, 45504, etc.

You can see that by looking at the icons after the values in your formula - they are multiple icons, compared to my formula, where the image is a single icon.

Implicit in my formula is a thisRow qualifier. In your formula, you need to uniquely identify, using a filter, a single value in the table Test Table Dates.

Please provide us with an example doc, then it will be very easy to show you in your own doc.

Regards
Piet

[Ilyssa_Russ]

Hey Piet,

Here is a sample doc for you to play with:

Add a New Hire at the top, and then click “Add Tasks.” That will populate the table below. I’d like the Duration dates in the Running Task list table at the bottom to interact with the New Hire Start date at the top. Thank you!

[Piet_Strydom]

Hi Ilyssa,

Thanks for the doc. I have broken the solution into steps.
(Done in the doc.)

1. Get the start date from New Hires Overview:
   
2. Get the relative date from the Running Task list table:
   
3. Add the duration to the start date:
   

I hope that gives you what you are looking for?

Regards
Piet

[Ilyssa_Russ]

Brilliant, Piet! AND I can just hide those Copy Columns so no one will be the wiser ◡̈ You are amazing!

[Piet_Strydom]

My pleasure.

You can always combine the two copy formulas into a single one. I don’t write formulas often, so like to do them step by step.

P