What I want to do is for example:

A time will be manually entered into a cell say D3, when it is 20 minutes before this I want cell D1 to change to "20 MIN", WHEN IT IS 5 minutes before I want to change the D1 text to '5 MIN' then when the time reaches now change to "IN PROGRESS", 25 minutes later to change to 'COMPLETE'. This same rule will apply for H - GS (as 4 columns are grouped together).D1 also has a data validation list that needs to stay available for manual override.

Will a script that can do this slow the spreadsheet down as well or not?

I have looked around for scripts but having no luck making it work.

  • Why don't you share a doc with us and show us exactly what outcome must be.
    – Jacob Jan
    Commented Jan 11, 2014 at 19:54
  • docs.google.com/spreadsheet/…
    – pdrops
    Commented Jan 14, 2014 at 22:48
  • I've added an example of what I am trying to do - the link is above - Essentially a start time will be entered in row 2 manually, this should then trigger the timer to change the the second cell in row 1 accordingly - from 20 min before the set time it will show 'first call', from 5 min to 0 it will show final call, once the time is now it will show 'in progess', after 25 min it will show complete, there needs to be an option to manually override - even if it means deleting the formula to access data validation that is ok. the time in row 3 is automatically 25 min after the set time in row 2
    – pdrops
    Commented Jan 14, 2014 at 22:53
  • Can't access the doc
    – Jacob Jan
    Commented Feb 8, 2014 at 20:22

1 Answer 1


I do not think you need a script.

I think you need a formula that looks at the difference between the value in D3 and the value of =now()-today() .

in you set E1 = now()-today() - d3

then you want something like

D1 = if( E1 < 0.20, "", (if E1 < 0.05, "20 MIN", ..... etc.

(I'm pretty sure that there are easier ways to build a complex if/then/else forumla for a cell, this gives you the general idea.)

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.