Google-sheets – Automatically apply new formatting and remove time from a dynamically added Date-Time in google sheets

google sheetsworksheet-function

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?

Best Answer

Highlight the data you want to format.
You can adjust the specific formatting for these values by clicking on the arrows in the value and choosing an option.
To delete a value from your formatting, click the value and select Delete.
Click Apply.

Related Solutions

Google-sheets – Conditional Formatting across a range of cells with a formula in Google Sheets

First of all, sharing your sheet or at least a copy/example is going to get you a much better answer more quickly.

I attempted to recreate your spreadsheet as best I could given your description (completely guessing on the calendar sheet).

Based on my example, all the cells in Calendar!B6:X37 contain a unique date. Each cell takes its date, and searches for it in 'Training Classes'!E:E. If it finds a match, then that cell gets highlighted yellow.

The formula I use for the conditional formatting is:

=ARRAYFORMULA(COUNTIF(INDIRECT("Training Classes!E:E"),B6:X37))

The formula you were using:

COUNTIF(indirect("Training Classes!$E:$E"),A1)>1

I believe this wasn't working because: You said the calendar was in B6:X37, yet you reference A1 (your working formula example uses S27). Also, you are missing an = at the start of the formula, and the >1 appears to be unnecessary.

Google-sheets – Conditional Formatting with Multiple Conditions Weekday and Time

Please try this one instead:
=AND((weekday(C$4:C24)=3),(EQ(RIGHT(C$4:C24,8),"21:00:00")))

Do take notice of both the $ signs. The formula will not work if you omit them.

PS: When asking try giving the URL to a sample sheet and if the answer works accept it so as to help others as well. (Why vote?)

Best Answer

Related Solutions

Google-sheets – Conditional Formatting across a range of cells with a formula in Google Sheets

Google-sheets – Conditional Formatting with Multiple Conditions Weekday and Time

Related Topic