How I can separate Date from created Date

Celenne Damian

I try to use the formulas next 2 formulas but no works, say (#INVALID COLUM VALUE)

=DATE [Created Date]@row)

=DATEONLY [Created Date]@row)

the only formula that works little bit is:

=MONTH([Created Date]@row) + "/" + DAY([Created Date]@row) + "/" + YEAR([Created Date]@row)

the problem is with this last formula some arrows is not the correct date, the correct date is from created day but when I apply the formula not all dates area correct, example in the picture:

I need help, I appreciate and idea.

Kelly Moore

Hey @PM_Reeves

If I am understanding correctly, you have a couple of issues? Your screenshot suggested you had the time zone issue where local dates are one date however smartsheet date is another date. This known issue occurs between the time that GMT-0 crosses midnight and your time zone crosses midnight.

As a second issue, you have a random occurrence of your sheet date format changing on linked data. (When you say linked, do you mean physical cell linking, or a lookup formula)? Is the data all coming from the same Project sheet, or multiple sheets rolled into one sheet? I ask as I wondered what the date setting was on the project sheet? Possibly are the anomalies coming from data entered by any specific user (or user country)? I'm sure these are questions asked in response to your submitted tickets. Sorry to be redundant.

Kelly

Kelly Moore

hey @Celenne Damian

Please check the column type where your formula is. I believe you are trying to pull a date into a number/text column. Once your column type is corrected you should be able to use this

=Created@row

kelly

Celenne Damian

@Kelly Moore Thank you for your answer, but the column format is correct and still happening, the created date is other day comparing with the column with the formula to pull the date from created date

Celenne Damian

I also use other formula (=DATEONLY) and the issue still happening

Kelly Moore

Hey @Celenne Damian

Sorry, I saw only the textstring formula was working. Your problem is a known issue of time zones. The Date only date is accurately showing what the behind the scenes database has stored. As listed below, the work around is to use Record Date when rows are added

https://community.smartsheet.com/discussion/77945/created-column-is-the-data-behind-this-column-not-local-time

Kelly

PM_Reeves

Hi @Kelly Moore,

I see you've mentioned a known issue with Time Zones. Do you have any further info on this at all?

The reason I ask is that I'm based in Adelaide (ACST), and occasionally one of my sheets will change the date format of a couple of random cells to Pacific Time. These cells are linked in from a Project Plan where the source data is correctly shown in ACST.

The process to get them back to my time is simple, but not sure how it works.

I simply open the sheet where the source data comes from, click ANY cell (doesn't need to be in the row, column, or even have any data in), then click Save. Once 'Refreshed', the data in my target sheet reverts to ACST.

It doesn't happen with any set pattern, and as far as I've checked, there are no constants with the cells that change.

I've raised a few tickets with Support, with no real resolution.

Thanks in advance.

Kelly Moore

Hey @PM_Reeves

If I am understanding correctly, you have a couple of issues? Your screenshot suggested you had the time zone issue where local dates are one date however smartsheet date is another date. This known issue occurs between the time that GMT-0 crosses midnight and your time zone crosses midnight.

As a second issue, you have a random occurrence of your sheet date format changing on linked data. (When you say linked, do you mean physical cell linking, or a lookup formula)? Is the data all coming from the same Project sheet, or multiple sheets rolled into one sheet? I ask as I wondered what the date setting was on the project sheet? Possibly are the anomalies coming from data entered by any specific user (or user country)? I'm sure these are questions asked in response to your submitted tickets. Sorry to be redundant.

Kelly

PM_Reeves

Thanks @Kelly Moore ,

All my errors appear on one sheet, which has cell links from each project. The source cell does have a formula whereby it takes the date and time a cell on the source sheet is updated.

With what you've said in here and what Mark Poole has said in another thread, it makes more sense to me now. I can sort of see the root of the cause.

Enjoy your weekend.

Celenne Damian

@Kelly Moore Thank you so much! you are a genius!!!

it works…

I separate correctly the date from created date

also I separate the time from created date

👏👏👏👏