Generate formula for compare dates and if condition is met, put custom text

Question

I'm working with Google Sheets and Google App Scripts¹ for retrieve search results from YouTube - by using YouTube Data API - and write those search results in batch on a sheet.

What I'm trying to accomplish is:

Compare the publish date of the video (values in "C" column) and compare it with the current date (i.e. today's date).

If the video was published today, then, write (on the cells in "E" column) the word: NEW!, otherwise, leave it blank.

I'm looking for a formula that can be set on the cell - rather than just modifying the script and clear the whole column (or a range) everytime the script starts running and rewriting the word if the condition is met¹.

I tried modifying these formulas shared on this answer and this answer, but, I haven't figured out how to deal with dates on Google Sheets.

Sample:

Video_ID	Title	Date Uploaded	Video link	¿Is New Video?	Desired output
rNp9qmASSlw	Privileged Victim Archetype? ~ Meghan Markle "Archetypes" Analysis	2022-08-25T04:37:48Z	https://www.youtube.com/watch?v=rNp9qmASSlw		NEW!
MkBQXzmyh1A	Did Gary Busey Add 'Horror' to Monster Mania Convention? ~ Gary Busey Case Analysis	2022-08-24T04:38:19Z	https://www.youtube.com/watch?v=MkBQXzmyh1A
q2QNjccf6hw	10 TRUE Disturbing & Terrifying Followed Home Horror Stories ~ Scary Stories To Fall Asleep To	2022-08-25T00:15:01Z	https://www.youtube.com/watch?v=q2QNjccf6hw		NEW!
-Imgs5T2A8w	50 TRUE Disturbing & Terrifying Scary Stories In The Dark ~ Horror Stories To Fall Asleep To	2022-08-22T03:19:25Z	https://www.youtube.com/watch?v=-Imgs5T2A8w
2hw4EPZLO_A	evento 12 de agosto pelea 11	2022-08-25T12:13:23Z	https://www.youtube.com/watch?v=2hw4EPZLO_A		NEW!
LY6unkFL3R4	evento 12 agosto pelea 10	2022-08-23T00:42:16Z	https://www.youtube.com/watch?v=LY6unkFL3R4
QHW9wddNJQE	WHEN A SMART NUMERON PLAYERS MEETS A STUPID NUMERON PLAYER	2022-08-25T00:16:52Z	https://www.youtube.com/watch?v=QHW9wddNJQE		NEW!
ol6Yt0TjB0E	WHEN YOUR OPPONENT IS THE MOST DUMB PLAYER EVER IN MASTER DUEL	2022-08-24T00:00:11Z	https://www.youtube.com/watch?v=ol6Yt0TjB0E
BisJI5BGloY	WHEN YOU DESTROY A THICC WAIFU BOARD WITH ONE SMALL MONSTER	2022-08-20T00:05:00Z	https://www.youtube.com/watch?v=BisJI5BGloY

This is the link of the sample spreadsheet.

---

¹ I'm using GAS for retrieve the data and I want to avoid calling for write/clear cells/ranges from the script due to GAS limitations - (if the script takes five minutes or more, a timeout exception is raised and the script is interrumpted).

Answer 1

try:

=ARRAYFORMULA(IFERROR(IF(TODAY()=INDEX(SPLIT(C2:C, "T"),, 1), "NEW!", )))

---

update:

delete everything in E column and use this formula in E1 cell:

={"¿Is New Video?"; ARRAYFORMULA(IFERROR(IF(TODAY()=
 INDEX(SPLIT(C2:C, "T"),, 1), "NEW!", "OLD VIDEO")))}