Hyperlinks in date cells prohibiting date's usage in formulas

Tori Heath

Hi Smartsheet Community,

I am trying to calculate the # of days, positive or negative, between two dates (depending if we're early or late compared to the date in our contract) but am running into issues when our team has added hyperlinks within date cells. You'll see below that I created a helper column to try to get around the hyperlink issue but the problem persists. The SC Actual Date Helper Column is simply =[SC Actual Date]@row. And the formula that I'm using to calculate the deviation in the days is =IF([SC Actual Date Helper]131 = "", "", [SC Actual Date Helper]131 - [SC Contract Date]131), which does work if there was never a hyperlink involved.

If anyone has figured out a solution for this I would love to know. Many, many thanks in advance!

Toufong Vang

Assuming that SC Deviation is SC Actual Date minus SC Contract Date, then the following will calculate that value for you.

=(DATE(VALUE(IF(RIGHT([SC Actual Date]@row,2)>RIGHT(Today(),2),"19"+RIGHT([SC Actual Date]@row,2),"20"+RIGHT([SC Actual Date]@row,2))), VALUE(LEFT([SC Actual Date]@row, 2)), VALUE(MID([SC Actual Date]@row,4,2)))) - [SC Contract Date]@row

The formula does not depend on SC Actual Date Helper. I'm assuming that you created the column in an effort to resolve the issue with the hyperlinked date in SC Actual Date. If that is not the case, then substitute the expression below in formulas where you need to include SC Actual Date

DATE(VALUE(IF(RIGHT([SC Actual Date]@row,2)>RIGHT(Today(),2),"19"+RIGHT([SC Actual Date]@row,2),"20"+RIGHT([SC Actual Date]@row,2))), VALUE(LEFT([SC Actual Date]@row, 2)), VALUE(MID([SC Actual Date]@row,4,2)))

The expression assumes that if the year part of the SC Contract Date date is greater than the current year, e.g., 12/31/99, then use "1999" instead of "2099" to calculate the SC Deviation.

EXPLANATION: The problem is that, the data in SC Actual Date looks like a date to you and me, but it is only "display text". The actual datatype, to Smartsheet, is a hyperlink. While we can parse it, trim it, etc., its "hyperlink" trait persists. It is the "hyperlink" datatype that is causing your formula to err. The expression above parses the display text--LEFT(), MID(), RIGHT()--and then converts it to a date datatype--VALUE(), DATE().

Cheers!

bcwilson.ca

I cannot even do this to test.. I am assuming they are copying the date from somewhere and pasting and Smartsheet is seeing it as a Hyperlink

Things to try

1) What if you turned on restrict dates only in the column properties? This would upset users but force them to change how they are pasting

2) Sometimes running these through a "scrubbing process" and converting them back...

Something like

=MID([SC Actual Date]@row, 1, 2) + "/" + MID([SC Actual Date]@row, 4, 2) + "/" + MID([SC Actual Date]@row, 7, 2)

That would work as long as no user decided to put in 1/1/21.. If the mid function does work you could create some logic to look for those "/"

Sorry that's all I have and cannot test as I don’t know how to create that hyperlinked date

Can you post a link to a copy of the sheet with data scrubbed and maybe something can be sorted?

Toufong Vang

Assuming that SC Deviation is SC Actual Date minus SC Contract Date, then the following will calculate that value for you.

=(DATE(VALUE(IF(RIGHT([SC Actual Date]@row,2)>RIGHT(Today(),2),"19"+RIGHT([SC Actual Date]@row,2),"20"+RIGHT([SC Actual Date]@row,2))), VALUE(LEFT([SC Actual Date]@row, 2)), VALUE(MID([SC Actual Date]@row,4,2)))) - [SC Contract Date]@row

The formula does not depend on SC Actual Date Helper. I'm assuming that you created the column in an effort to resolve the issue with the hyperlinked date in SC Actual Date. If that is not the case, then substitute the expression below in formulas where you need to include SC Actual Date

DATE(VALUE(IF(RIGHT([SC Actual Date]@row,2)>RIGHT(Today(),2),"19"+RIGHT([SC Actual Date]@row,2),"20"+RIGHT([SC Actual Date]@row,2))), VALUE(LEFT([SC Actual Date]@row, 2)), VALUE(MID([SC Actual Date]@row,4,2)))

The expression assumes that if the year part of the SC Contract Date date is greater than the current year, e.g., 12/31/99, then use "1999" instead of "2099" to calculate the SC Deviation.

EXPLANATION: The problem is that, the data in SC Actual Date looks like a date to you and me, but it is only "display text". The actual datatype, to Smartsheet, is a hyperlink. While we can parse it, trim it, etc., its "hyperlink" trait persists. It is the "hyperlink" datatype that is causing your formula to err. The expression above parses the display text--LEFT(), MID(), RIGHT()--and then converts it to a date datatype--VALUE(), DATE().

Cheers!

Tori Heath

Thank you @Toufong Vang - that worked great! Very much appreciated.