0

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?

migrated from superuser.com Apr 14 '18 at 8:11

This question came from our site for computer enthusiasts and power users.

  • 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 '18 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 '18 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 '18 at 18:13
  • Questions about Google Sheets should be asked on the WebApps site rather than Super User. – Blackwood Apr 11 '18 at 3:31
0
  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 '18 at 17:59
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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