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

 

 

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

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. 

Resalat Kabir

Dear all

 

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

Appreciate all your advices

Andrée Starå

Great!

I'm always happy to help!

Best,

Andrée

Mike Wilday

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