I am new to Google sheets and don't know how to write excel/google sheet formula to apply automatic formatting to a date+time value added through an external script.

I have a CRM integrated with google sheets, and it dynamically adds current date+time to a google sheet column.

The date format it adds is 2018-04-09T17:49:59 but I want this format to be changed to 2018-04-09 and time(T17:49:59) removed. So the final product is only date 2018-04-09

Here is the image that shows what I meant.

Column C represents what is being added by the CRM and Column D is what I want it to be.

Can anyone please help me to write the proper formula that will format the date and remove the time?

  • You don't need to change the formula. Format the cells to display the format you want, which is yyyy-mm-dd. More details can be found here.
    – Bandersnatch
    Apr 10, 2018 at 14:16
  • Sorry but its not clear to me. You said "you don't need to change the formula". But I don't have any formula. And the link you sent.. it does not tell how do I remove/delete "T17:49:59" from above example
    – absikandar
    Apr 10, 2018 at 17:53
  • 1
    In your question's title, you wrote "formula to format date". You don't need a formula. Here is how to change the format of the cells.
    – Bandersnatch
    Apr 10, 2018 at 18:13
  • Questions about Google Sheets should be asked on the WebApps site rather than Super User.
    – Blackwood
    Apr 11, 2018 at 3:31

2 Answers 2


Finally I am able to solve the problem. I used =REGEXEXTRACT(C5,"(.*)T.*") to remove the Time from that string. And going to use the date format as is for now.

The second answer on the following stackoverflow question helped me. https://stackoverflow.com/questions/35164557/remove-characters-from-a-string-after-a-certain-word-excel

  1. Highlight the data you want to format.
  2. You can adjust the specific formatting for these values by clicking on the arrows in the value and choosing an option.
  3. To delete a value from your formatting, click the value and select Delete.
  4. Click Apply.
  • Thanks for the answer but this is not what I am looking for. I have updated my question, I hope that makes more sense.
    – bakar
    Apr 16, 2018 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.