Python API - How do I transfer a date-time value from a Pandas data frame to a new row?

edited 07/18/22 in API & Developers

Hi! This is my first question in the forum, so please let me know if I'm doing something wrong.

I have some data in an excel csv file, and I have copied it into a Pandas data frame. One of the fields in the csv has this format: "3/17/2022 10:59:00 AM" and when transferred to the data frame it shows '3/17/2022 10:59'. So I convert it to what I think is the right Smartsheet format with this line, 'Created' being the column name:

data_frame['Created'] = pd.to_datetime(data_frame['Created']).dt.strftime("%Y-%m-%dT%H:00:00Z") 

and ends up being '2022-03-17T10:00:00Z'.

When I create the smartsheet row using a loop and


the field shows in smartsheet as '2022-03-17T10:00:00Z'. And if I use a formula like =TODAY() - [Created Date]@row, I get an error #INVALID OPERATION.

How can I get it right?

If I use "%Y-%m-%d" it does not work, I get 2022-03-17 but it still throws an error in the sheet.

If I do something like


it works, but to do that I would need to split the loop and it may overcomplicate things.


  • Genevieve P.

    Hi @jzinna

    If I'm understanding you correctly, you can get the information into Smartsheet with this:


    But the information brought in is formatted as text ('2022-03-17T10:00:00Z') so the cell in Smartsheet stores it as text and therefore you can't use a formula such as =TODAY() - [Created Date]@row

    What I would do in this instance is keep your code as-is, but set up a helper column in the sheet to translate your text to a date using the DATE function:

    =DATE(yyyy, mm, dd)

    We can grab these numbers from your string as long as they will always come in as 2022-03-17 or YYYY-MM-DD.

    For the Year:

    VALUE(LEFT([Created Date]@row, 4))

    For the Month:

    VALUE(MID([Created Date]@row, 6, 2))

    For the Day:

    VALUE(MID([Created Date]@row, 10, 2))

    For a full formula:

    =DATE(VALUE(LEFT([Created Date]@row, 4)), VALUE(MID([Created Date]@row, 6, 2)), VALUE(MID([Created Date]@row, 10, 2)))

    Then once this is translated into a Date, you can use your formula:

    =TODAY() - [Helper Date]@row

    Or you could build it all into one formula:

    =TODAY() - DATE(VALUE(LEFT([Created Date]@row, 4)), VALUE(MID([Created Date]@row, 6, 2)), VALUE(MID([Created Date]@row, 10, 2)))

    See: MID Function / LEFT Function / VALUE Function / DATE Function



    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now