Time/Date Stamp formulas

06/29/19 Edited 12/09/19

Hi, 

I am trying to auto populate a time date stamp when a cell is filled in on a row. Ex Submission Status

then I need to create a formula to populate a due date based on the time date stamp and based on Priority Levels

Priority Level  (Business Days)           

High - 3 days            

Medium - 5 days 

Low - 10 days 

NBD<12pm- (Next Business Day before 12pm )

2BD>12pm - (2 Business days after 12pm.)

 

I have attached a copy of my sheet to give you and idea of what I am trying to do. 

Any help is appreciated. 

 

                                          

Permit Drawing Submittal.PNG

Popular Tags:

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Kate,

    Zapier is a great option for this scenario. Is that an option for you?

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • edited 07/08/19

    I have not heard of Zapier. I am not sure my company would pick this up. 

    How would I incorporate that into Smartsheet?

  • Unfortunately, Zapier is not an option for me. 

     

  • I have changed my submitted date column sot auto-number system created date. 

    that will give me a time/date stamp. 

    How would I create a formula that will add my working business days (not including holidays) based off of my priority levels, and the submitted date, to return the due date?

    Ex Work week M-F Can not include holidays

           Priority levels: High add 3 bus.days, Med add 5 bus.day, Low add 10 bus.               days, Next biz day < 12 add 1 bus.day, Next Biz >12 add 2  bus.days 

            Then return due date. 

    Is this possible with the time date stamp (06/28/19 12:28 PM)

    All help is appreciated. 

          

  • Here is my Unparseable error. 

    =DATEONLY([Date Submitted]@row + 10),AND([Priority Level]@row="Low"))

     

    =Dateonly[Date Submitted]@row+10) adds the way I need it to.

    My problem is that I want it to add only if it meets "Low"=10 days, "Medium"=5 days, "High"= 3 days

    This should inturn return the due date based on the criteria. 

    I am having a hard time writing the formula

    I hope I have explained it clear enough. 

    SnapShot.PNG

  • Thank you for the help. 

    I did  manage to get the formula to work myself. 

     

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Excellent!

    Happy to help!

    What formula did you end up with?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.