Looking for formula

Dilya

I'm new to using Smart Sheets and looking to calculating aging date for invoices for tracking purpose. I tried several ones I used for excel but does not seems to work here. I did also use some of the formula's people had suggested on the blogs here but no luck. Please help.

What I have is the invoice date and need to know how many days it's been open but any formula I enter is not working, shows "unparseable".

Mike Wilday

Here is the general explanation of the #unparseable error: https://help.smartsheet.com/articles/2476176-formula-error-messages

Sometimes its an issue with the column name not being spelled correctly, sometimes its an issue with quotation marks having been formatted in a word processer to make them smart quotes, sometimes its because you are trying to return the value to the wrong column type. 

Can you post the example of your non-working formula here so we can review it and make suggestions? 

Paul Newcome

=TODAY() - [Column Name]1

Try the above formula. It should update every time the sheet is opened based on the current date because of the "TODAY()" function being used.

(See attachment for example)

Mike Wilday

Replace "Column Name" in Paul's example with your own if that is what you are trying to accomplosh. Also, you need to make sure you are putting it in a column that is not formatted for Date, or something else. Make sure it is a text/number column type by right-clicking on the column header. Also, if you have a completion column or status column you can modify your formula to ignore it if its completed. 

=IF([Completion Checkbox Column Title]23=1, "Complete", TODAY() - [Date Column Title]23

Or if completion is signfied with a dropdown or with text, try 

=IF([Completion Checkbox Column Title]23="Complete", "Complete", TODAY() - [Date Column Title]23

Dilya

Hello,

 

I tried entering Paul's formula and same result. Please take a look at the template I had on excel and exported on here but can't seems to figure out the formula part and how to calculate the age of the invoice from the date it was issued "invoice date". Please help!

 

Thanks.

Paul Newcome

I just plugged this in, and it worked fine for me. Make sure your Invoice Date column is of the Date type and that your Age column is the Text/Number type. If that doesn't do it for you, you can put the formula you are using that is failing up here, and we can take a look at it.

 

=TODAY() - [Invoice Date]1

Paul Newcome

I just noticed this... Your Invoice Date column is your Primary Column (note the bold text in the column header). Primary columns can only be the Text/Number type.

 

Simply Right Click your Invoice Date header and rename it. Then Right Click that same header again and Insert Column Right. You can then name your new column "Invoice Date" and select the Date type.

 

Move all of your dates over to the new column, and that should do it for you!

Dilya

Hi Paul,

Truly appreciate all you help and I did make the changes you suggested and entered the formula =Today()-[invoice date]1 and as you can see on the photo it's blank.

My date column is filtered to "Date" and Age column to "Text/Number...not sure what am I doing wrong at this point.

 

 

Dilya

Paul! thank you I figured out I had to change "13" to the row number and to make it automatic for that entire column do I just drag?

Paul Newcome

Correct! Using the drag-fill is the most efficient way to populate the rest of the column, and it will automatically update the row reference for you as well.

 

Glad we were able to get that figured out. 

Dilya

Paul, would you be able to tell me if I'm able to keep the aging portion on top where I have 1-30days 31-60 and etc and enter formula's to get the total amount of the invoices that fall under that category? Where it shows the Aging Summary and it's blank with "equal sign"I want to show todays date and I tried entering "=today" or can not be done due to the column being text/number column?

Mike Wilday

Hi Dilya, 

You can convert the =Today() formula to return a current date in a Text/Number field by converting it using the Month, Day and Year fomulas concatenated in a string with / slashes added in. This one is set as a U.S. Date. 

=MONTH(TODAY()) + "/" + DAY(TODAY()) + "/" + YEAR(TODAY())

Andrew Stewart

Hi Dilya,

Further to Mike's suggestion, just in case you have not worked out the formulas yet, try something like

==SUMIFS([Outstanding $$$]:[Outstanding $$$], [Invoice #]:[Invoice#], <>"",[Age]:[Age],>30,[Age]:[Age],<=60)

to get the outstanding value of invoices for the 31 - 60: cell. Because only columns are referenced, you won't have to keep changing the formulas. The ageing portion itself is ignored because of the condition that the invoice # not be blank, if you need to put text there find a different condition to check.

Another tip which would have saved me a lot of errors when I started out with Smartsheet is to change any formulas like 

=Today()-[invoice date]1

to =Today()-[invoice date]@row

The value @row just meant the current row number. It is much easier to cut and paste formulas and end up with the intended result that way!

Paul Newcome

I am going to agree with Mike on the date formula.

 

 

As far as the other part goes, you have two options:

 

1. Move your summary table to a different set of columns.

    Where it is now, you will create a "Circular Reference" if you use column

    references in your formulas.  

 

 

2. Make specific row references in your formulas.

    This will allow you to leave your table where it is, but you will have to update

    the formula with the new row number every time you add a new line or use a

    row reference that is well below where you need to be. Either way, with this 

    option you will still end up needing to update the bottom row reference once

    you go below that point.

Dilya

Mike, truly appreciate you help! It worked:)

Mike Wilday

Glad I could be of help!