How to set up a formula to get around timestamps?

Paige_Hamby

I have the search send over as a csv, but it has the date field formatted differently than how Smartsheet sees date fields. I occasionally run into this issue when I use the date field from the system notes in NetSuite. Is there a formula that could be set up to get around this? I have the column set up as a date field already. NetSuite sadly won't send over as a xlsx which would fix this issue, so I need to try and find a way around it if possible.

Eric Law

@Paige_Hamby would =DATEONLY work for you? Also, if you can get your date to have floating 0 in front of the single digits you could also do =LEFT

Paige_Hamby

@Eric Law No it won't let me do DATEONLY because it doesn't see it as a date_time I'm assuming. With how the data comes over from NetSuite, it doesn't include the 0 in front of the single digits (which is really the main issue that makes it unable to extract the date). I'm not sure if there's a way to add it in once in Smartsheet?

Eric Law

@Paige_Hamby I found a post that will help you with your situation. In brief, you can use the following formula.

=LEFT([Date]@row, FIND(" ", [Date]@row) - 1)

Paige_Hamby

@Eric Law so that did get the date portion extracted technically? but it's still not seen as a date :( so I can't gather the month and year from the field. Think it's because it's not formatted the way smartsheet sees dates (01/25/2023)

Eric Law

@Paige_Hamby You will need 3 more helper columns then. Name them Year, Month, Day and it will be good to keep them in that order. Here are you formulas. The formulas for Month and Day adds a 0 in front if it is single digit.

Year: =RIGHT([Date Only]@row, 4)

Month: =IF(LEN(LEFT([Date Only]@row, FIND("/", ([Date Only]@row)) - 1)) = 2, LEFT([Date Only]@row, FIND("/", ([Date Only]@row)) - 1), 0 + LEFT([Date Only]@row, FIND("/", ([Date Only]@row)) - 1))

Day:=IF(LEN(MID([Date Only]@row, FIND("/", [Date Only]@row) + 1, FIND("/", [Date Only]@row) - 1)) = 2, MID([Date Only]@row, FIND("/", [Date Only]@row) + 1, FIND("/", [Date Only]@row) - 1), 0 + MID([Date Only]@row, FIND("/", [Date Only]@row) + 1, FIND("/", [Date Only]@row) - 1))

Date Date (your date column): =JOIN(Day@row:Year@row, ",")

Wish I could make it easier, but maybe you can fine tune it. Hope this helps! Verified it worked.