ADD 3WEEKS, , 2WEEKS, 1WEEK AND 24 HOURS DATE
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
Best Answer
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
You could use =DATEONLY(Insert cross sheet reference here) + 14) WORDAYS to rule out weekends.
-
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
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Jeff
You are correct. Once I changed the column to Date, everything worked.
Thank you very much
Rob
-
@Rob_PM Happy to help, glad it worked!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Never mind I found the problem. Thank you!
-
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
-
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.)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!