How I can separate Date from created Date

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.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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

Answers

  • Kelly Moore
    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

  • @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

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 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

    Kelly

  • PM_Reeves
    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    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.

  • @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

    👏👏👏👏

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!