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

Rob_PM
Rob_PM ✭✭
in Formulas and Functions

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.

3 weeks.png

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

Tags:
· Share on FacebookShare on Twitter

Best Answer

Answers

  • Jeff M.
    Jeff M. ✭✭✭

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

    · Share on FacebookShare on Twitter
  • Rob_PM
    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

    BTM copy.png

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

    Citrix communication.png

    I appreciate your help very much!

    Rob

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    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.

    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!

    · Share on FacebookShare on Twitter
  • Rob_PM
    Rob_PM ✭✭
    edited 01/24/22

    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

    Citrix1.png

    =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

    citrix2.png

    I am doing something wrong

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    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.

    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!

    · Share on FacebookShare on Twitter
  • Rob_PM
    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

    image.png


    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    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

    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!

    · Share on FacebookShare on Twitter
  • Rob_PM
    Rob_PM ✭✭

    Jeff


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


    Thank you very much

    Rob

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @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!

    · Share on FacebookShare on Twitter
  • Rob_PM
    Rob_PM ✭✭
    edited 01/27/22

    Never mind I found the problem. Thank you!

    · Share on FacebookShare on Twitter
  • Rob_PM
    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


    image.png


    Can you help me?


    Rob

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    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.)

    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!

    · Share on FacebookShare on Twitter
  • Rob_PM
    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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!