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

Peppey

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

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.

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

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

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.

Brian

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!

Best,

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!

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.