Auto Date Calculation

Resalat Kabir

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

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