ADD 3WEEKS, , 2WEEKS, 1WEEK AND 24 HOURS DATE

Rob_PM

I need a formula that calculates 3weeks date, 2 weeks date, 1 week date and 24 hours date from a due for each column where the due date is another sheet.

Let's say column Citrix status in another sheet is feb/3/22, so under Citrix Status 3weeks from the due date will show in that column and so on

I appreciate your help

Rob

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317171#Comment_317171

Are both your Citrix Migration Date column in "TEST" and your CMDate column in "COMMUNICATIONS" set as Date-type columns?

#INVALID COLUMN VALUE

Cause

The formula contains or references a data type that is inconsistent with the column type where it is inserted. 

Formula Error Messages | Smartsheet Learning Center

Jeff M.

You could use =DATEONLY(Insert cross sheet reference here) + 14) WORDAYS to rule out weekends.

Rob_PM

Jeff

How do I insert cross sheet reference for that column and cell? What is the formula? As you can see it I needs to link Citrix Migration Date for the row FL310

then the result should populate for these 4 categories in another sheet under Citrix Status Column:

I appreciate your help very much!

Rob

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317140#Comment_317140

One thing that Smartsheet does not make easy is being able to create "sets" of formulas to be applied to every group of child rows under a parent row. So once you have your formulas, they will need to be applied individually under each parent row. I would recommend changing your four calculated date locations from child rows into columns in the sheet, if possible. But barring that...

The first thing I would do would be to bring the value for Citrix Migration Date into the parent row on your second sheet. You can create a hidden helper date-type column (call it CMDate,) and use an INDEX/MATCH formula. When you start typing =INDEX( Smartsheet will start helping you. Click "Reference Another Sheet" and select the Citrix Migration Date column, and do the same for the first sheet's location column for the MATCH portion of the formula.

=INDEX({Sheet 1 Citrix Migration Date}, MATCH(Location@row, {Sheet 1 Location Column}, 0))

Now that you have the date in your parent row, add calculated date formulas to the child rows:

3 weeks: =PARENT(CMDate@row) + 21

2 weeks: =PARENT(CMDate@row) + 14

1 week: =PARENT(CMDate@row) + 7

24 Hours: =PARENT(CMDate@row) + 1

Repeat the same for your other three categories.

If you don't want to bring the date values into the parent rows, you can calculate the same with individual INDEX/MATCH formulas, but keep in mind that if you have a 1000s of rows in either sheet, the fewer cross sheet references you have, the better your sheet's performance will be:

=INDEX({Sheet 1 Citrix Migration Date}, MATCH(PARENT(Location@row), {Sheet 1 Location Column}, 0)) + 21

etc.

Rob_PM

Jeff

I am doing something wrong. The sheet that provides the Citrix date now is call TEST and the sheet to collect the 3weeks, 2 weeks, 1 week and 24 hours is called COMMUNICATIONS

I created the CMDate column in "COMMUNICATION" sheet and added the formula

=INDEX({TEST Citrix Migration Date}, MATCH(Location@row, {TEST Location Column}, 0))

The sheet that provides the Citrix Migration Date is called TEST as you can see below

I am doing something wrong

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317155#Comment_317155

When creating your formula, did you create the sheet references? make sure when you do, that you select the entire column by clicking on the column header.

When you start typing =INDEX( Smartsheet will start helping you. Click "Reference Another Sheet" and select the Citrix Migration Date column, and do the same for the first sheet's location column for the MATCH portion of the formula.

Rob_PM

Jeff

In the sheet 1 "Communications" I added the formula in the CMDate column

=INDEX({TEST Range 1}, MATCH(Location@row, {TEST Range 2}, 0))

TEST Range 1 is whole column for Citrix Migration Date column (sheet 2 "TEST")

TEST Range 2 is whole column for whole Location Column (sheet 2 "TEST")

Sheet 1 = "Communications" (where I want to populate the 3 weeks date)

Sheet 2 = "TEST" (where I want to get the date)

I got #INVALID COLUMN VALUE

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317171#Comment_317171

Are both your Citrix Migration Date column in "TEST" and your CMDate column in "COMMUNICATIONS" set as Date-type columns?

#INVALID COLUMN VALUE

Cause

The formula contains or references a data type that is inconsistent with the column type where it is inserted. 

Formula Error Messages | Smartsheet Learning Center

Rob_PM

Jeff

You are correct. Once I changed the column to Date, everything worked.

Thank you very much

Rob

Jeff Reisman

@Rob_PM Happy to help, glad it worked!

Rob_PM

Never mind I found the problem. Thank you!

Rob_PM

Jeff

When I changed the formular you gave me for =PARENT(CMDate@row) - 21. Using the help column CMDate to another column in my sheet "Network Migration Date, the formula didn't work. =PARENT(Network Migration Date@row) - 21

Can you help me?

Rob

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318985#Comment_318985

Notice how the "Network Migration Date@row" in your formula isn't color-coded? That's because the system isn't picking up your reference. For the system to recognize column names with more than one word, or column names ending in a number, you need to surround the column name in [ brackets ].

So your formula would be: =PARENT([Network Migration Date]@row) - 21

(Also shouldn't it be + 21? I thought you were looking for the date 3 weeks in the future.)

Rob_PM

Thanks Jeff.

I created another posting that has to do with this + setting up automation to alert someone. I would like to know if you could help me with that:

ALERT SOMEONE — Smartsheet Community

Thank you very much

Rob