Splitting DATE and Time column with Date Only in a new column

Peppey ✭✭
edited 01/16/24 in Formulas and Functions

Hi everyone! I am hoping you can assist me. I have a column titled "Appt Date/Time" that has the date and time. Example: 1/1/2000 5:00. I have tried using the DATEONLY formula in a new column to extract just the date but keep getting an INVALID DATA TYPE error. I have verified the new column is a date column. Can someone assist me please?


  • AravindGP
    AravindGP ✭✭✭✭✭

    Hi @Peppey

    In all likeliness, your Appt Date/Time is a Text/Number column type. You can use the Dateonly formula only when the column type being referenced is a date column type (usually the time is added when it is a system generated field like created date or modified date). Since these are going to be filled in manually, you can try using this formula "=LEFT([Appt Date/Time]@row, FIND(" ", [Appt Date/Time]@row) - 1)". This will pick up just the value before the space that is used for entering time by your users.



    Reach out for any help on licenses, configuration, or training

  • Peppey
    Peppey ✭✭

    Thank you so much this worked perfectly. I am sorry to ask but I just recevied another requirement. How can I extract just the month number from the Appt Date/Time column? I tried the left formula but I am having problems because some months (i.e. January) only has a "1" and other months like November have two (i.e. 11). I only want it to show the month number in this new column. Can you please assist me with that?

  • Anjanesh Vaidya
    Anjanesh Vaidya ✭✭✭✭

    Hi Peppey,

    You can use the 'Month' Function to extract only the month number from the date column.

    =MONTH([Appt Date/Time]@row)

    Thank You!

    Anjanesh Vaidya

  • Peppey
    Peppey ✭✭

    Hi Anjanesh!

    I hope you are well. I tried that and it is not working. I believe based on my research the reason it is not working is because its a test column. When I change it to a date column the HH:MM disappear. I tried a LEFT formula but that does not work. The main issue of it is when I do a report and try to filter between just dates using the LEFT formula no data appears so I am trying a work around with just the month number. Thanks for all your help.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Peppey

    I hope you're well and safe!

    Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    I hope that helps!

    Be safe, and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Peppey
    Peppey ✭✭

    Yes sir. The below columns are test and we receive them on an excel file and they are copied and pasted into SS. These two columns are what I believe are the main problems

    What we are trying to do is to create a report so if the date is between a certain time frame (i.e., December 1, 2023 - December 31, 2023) it will show all people with an enrollment date and the same with Appt Date but a different formula. The columns are text. I tried removing the hour with a LEFT formula so its just date in some helper columns but when I filter the report that meets the criteria no data appears.

    I really appreciate the help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!