Can't perform date calculations on a Date returned by a cross sheet JOIN(COLLECT(VLOOKUP function

Kevin Segrist

Hi,

To populate DueDate3, I'm using:

=JOIN(COLLECT({GUT Column 7}, {GUT Column 2}, <>"Postponed", {GUT Column 2}, <>"Rescheduled", {GUT Column 1}, VLOOKUP(TaskName1, {GUT Column 1}, 1, false)))

to return a date from another sheet. (It is a date data type in the other sheet).

In another cell, I want to use something like =DueDate3 - 28 to be used as a threshold date for conditional formatting, but getting an #INVALID OPERATION error.

Is the returned date no longer a DATE data type? How can I turn it into a DATE so I can perform DATE operations on it?

Thanks for any help!

Paul Newcome

The JOIN function converts the output to a text value. You would need to either use an additional column to convert the JOIN output into a date (extra column), nest the JOIN formula into VALUE/MID formulas to pull portions to nest inside of a DATE function (very bulky), or we can try a different function altogether.

Without seeing the source data I can't tell if it can be simplified beyond using an AND function for the {GUT Column 2} criteria. I am not sure exactly the function of your VLOOKUP, so I am going to leave that portion as is and use an INDEX instead of a JOIN.

=INDEX(COLLECT({GUT Column 7}, {GUT Column 2}, AND(@cell <>"Postponed", @cell <>"Rescheduled"), {GUT Column 1}, VLOOKUP(TaskName1, {GUT Column 1}, 1, false)), 1)

Kevin Segrist

Hi Paul,

Thank you for the very quick response!

I wound up using:

= DATE(VALUE(MID(DueDate$3, 7, 2)), VALUE(MID(DueDate$3, 1, 2)), VALUE(MID(DueDate$3, 4, 2))) - 28

Which seems to be doing what I wanted.

If you're interested, there is a sample of what I started with here:

https://community.smartsheet.com/discussion/68433/vlookup-with-duplicate-data

Thank you again!

Paul Newcome

I assume you put that into another column? If so, that would be what I would have suggested for my first bit where we convert the JOIN into a date value.

Kevin Segrist

In fact, I used the same column, but yes, I did have to convert the result to a date format, as you suggested, which is what I was struggling with. In the screenshot below, DueDate3 is returned from the master sheet, (since it points to a dummy record, the alternate conditional value is "TODAY()"), and all columns beneath are based on that date.

I have had a lot of fun learning to use Smartsheet, and I'm sure much of what I'm doing is very unorthodox. I can't say enough good things about you and others for your attentiveness and timely support! Thanks tons! :)

Paul Newcome

Ah. Ok. The screenshot helps clarify.

I am kind of curious what you mean by...

"(since it points to a dummy record, the alternate conditional value is "TODAY()")"

Are you just trying to pull the date generated by the TODAY function?

As for the support... Happy to help! 👍️

Kevin Segrist

No, this is intended to be used as a template... but since templates don't retain cross-sheet formula functionality, it's just a regular sheet that the user saves a copy, double-clicks "Previous Weeks", (the first row in the master sheet), and selects his/her client, and all the rest auto-populates.

The TODAY() function is used because there is no data in the "Status" column of the master sheet, (a dropdown with nothing selected), returns nothing, leaving a blank cell, causing #INVALID DATA TYPE, which looks ugly. I even added a third contition in my Collect() to check for "", but still got a blank cell. There is a date in the master "UpgradeDate" column, but it's a meaningless date, so TODAY() works. It would make more sense if I could show you the master sheet, but sensitive data, etc...

I'm sure as I become more proficient I'll look back and laugh at this overall approach. In any case, this suits my needs quite well for now.

Paul Newcome

You are correct that TEMPLATES do not retain cross sheet references, but sheets do. I personally save a template in my own sheets as a back-up, but then have people "Save as New" from a sheet that is just called a template. This way cross sheet formulas and cell links can be maintained.

As for the error, whatever formula is generating it can be wrapped in an IFERROR to generate a blank instead of the error like so...

=IFERROR(original_formula, "")

Kevin Segrist

Yes, "Save as New" is exactly what I tell my users too, and of course, I keep a backup of the latest version, in case someone doesn't follow instructions, or accidentally forgets. I had previously included instructions in row1/col1 on how to link to another sheet, but creating the link in my template sheet to that dummy record makes it so much simpler... double-clicking makes it perfectly obvious, since the dialog loads the master sheet already being used.

I will give IFERROR a try, thank you for that!

Paul Newcome

Happy to help! 👍️