Date field minus 'TODAY()

Hello All,

Could someone please explain why the following doesn't work or a solution:

I have a date column with a mix of dates both past and future. The next column is a column to say how many days till this date occurs. Therefore if it is in the future (i.e. date is the 30/10/20) then the second column needs to say 534. If it was it the past (i.e. date is the 13/11/18) then the second column needs to say -183.

I have managed to get it working in excel with a simple formula in the second column '=DATECOLUMN-NOW()' but unfortunately '=DATECOLUMN-TODAY()' won't work in SmartSheet.

Thanks in advance.

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi Lachlan,

It should work.

Try this.

=Date@row - TODAY()

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Lachlan Stead

Thanks Andrée,

I probably didn't explain myself properly. The date column is called 'Warranty Expiry' and the next column is called 'Warranty Days Left'. In the 'Warranty Days Left' column I am trying to use the formula:

=[Warranty Expiry]1 - TODAY()

Andrée Starå

I think we're talking about the same thing.

Try this.

It's the same as your but with @row.

=[Warranty Expiry]@row - TODAY()

Did it work?

Let me know if I'm misunderstanding something.

Best,

Andrée

Lachlan Stead

Thanks Andrée but no luck. See below:

Andrée Starå

What column type is the Warranty Days Left?

Best,

Andrée

Lachlan Stead

Text/Number

Andrée Starå

That's strange!

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Best,

Andrée

Lachlan Stead

Have done. There is just a one liner that's dummy data. LMK how you go or if I am missing something. Thanks.

Lachlan Stead

Thanks Andrée, I have found what the issue was. Thanks for your time.

Andrée Starå

Excellent!

What was the issue?

Best,

Andrée

Lachlan Stead

I was spending too much time on the formula and checking the 'Warranty Days Left' columns type rather than checking the 'Warranty Expiry' column type. This was Text/Number hence the formula couldn't pickup that value minus 'today'.

Pays to be thorough in initial setup, but missed it due to importing an excel doc ???

Andrée Starå

We've all been there!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.
Nested IF Statements
Hello. I cannot figure this out. =IF([GL Code]@row = 14030, "Leasehold") How do I add additional IF statements in the same cell, for example if the GL Code@row = 14040, "FF&E", GL Code@row = 14050, "CR Equip" Thanks.
Original sheet is corrupted or has some hidden issue preventing the formula from working?
I have a sheet where a formula in one column is not being applied . However, when I save the sheet as new, the formula works as expected. Is it possible that the original sheet is corrupted or has some hidden issue preventing the formula from working?