How I can separate Date from created Date

Celenne Damian · 05/27/24

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 · 05/31/24

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 · 05/27/24

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 · 05/29/24

@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 · 05/29/24

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

Kelly Moore · 05/30/24

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

Created column: Is the data behind this column not local time?

Terry Lewis

Apr 2, 2021

I have a form (sheet) in which I allow the user to enter a date, or leave the cell blank to use today's date.
On the sheet, a second column (Period) looks at the user entered date column (Entered date) and uses it, or if blank uses the system Created date.
I found instances where the date brought from the system Created column is TOMORROW. I have only two instances currently to look at, but they were both evening my time and I'm speculating the data in that system cell is actually UTC time, not local. It is displayed in my local time--but is it actually UTC or a different time?
More evidence: I've found two consecutive entries from an earlier date in which my colleague made one entry at 6:59 p.m. CT which is 23:59 UTC and then she made another entry at 7:00 p.m. CT which which is midnight UTC. Sure enough - copy and paste the system dates into regular date columns and they show as the correct UTC date, no local.
How can I compensate? Does the new "Record a Date" automation do the same thing?

Kelly

PM_Reeves · 05/30/24

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 · 05/31/24

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 · 05/31/24

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 · 05/31/24

@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

👏👏👏👏

Formulas and Functions

Formulas and Functions

How I can separate Date from created Date

Best Answer

Answers

Created column: Is the data behind this column not local time?

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA