Auto Date Calculation

Hi

Very new to SmartSheet, I am trying to create a Purchase Tracking sheet that can be used for Payment Schedule.

Here is what I have " Invoice Date" This I put it manually whenever I receive an invoice from my vendor.

I also have another cell "Droplist" which state payment terms like 30/60/90 days.

What i need is 

1) A Separate cell inputed as a column, which will use the Invoice date Add the Term Date and Give me a Payment Date

2) I have a column which auto-populate the current date. hence I need a separate column when today's date crosses the payment date, It auto fills the overdue date and the entire row goes RED.

Please tell me if this is possible and if so how can it be achieved.

 

Thanks in Advance

Comments

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi

    Yes - this is all possible.

    You will need to use Formulas to add your term to your invoice date to get a Payment date.

    eg (this would go in the [Payment Date]1

    =[Invoice Date]1 + [Term Date]1

    You could use an IF function to determine is something is Overdue OR you could simply use conditional formatting to colour the row when something is overdue.

    The logic for both would be if todays date is greater than the payment date then colour the row red.

    However, all your rows will go red once the payment date has passed, so you will also require an indicator of whether the payment has been made.  This could be actual payment date or a checkbox to show payment made or something. 

    If you include this extra column, your logic for conditional formatting would be if todays date is greater than payment date AND the payment indicator is showing that payment is still outstanding, then colour the entire row red.

    If you need further help, then I run remote workshops in how to use Smartsheet, with the greatest respect, as you are new to this app, this might be something worth considering.

    Kind regards

    Debbie Sawyer Consultant & Training Manager

     

     

    Smarter Processes mean happy people in successful businesses

     

     

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

    Hi Resalat,

    Try this.

    =[Invoice Date]@row + Droplist@row    

    The same version but with the below changes for your and others convenience.  

    =[Invoice Date]@row + Droplist@row

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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 for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    One thing to be aware of these solutions, your dropdown should just contain the number... 30, 60, or 90. Make sure it doesn't include the word days or your math won't work. 

    I agree with Debbie's statement about using multiple conditions in your conditional formatting. To do that click the little drop down arrow next to the condition that you are creating and choose add condition. See the screenshot for an example. 

    2019-03-04_08-50-57.gif

  • Dear all

     

    Thanks for the update , i have got it to work.

    Appreciate all your advices

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

    Great!

    I'm always happy to help!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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 for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Awesome. Glad we could help you out! Happy Smartsheeting! 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!