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 Tuinstra Jan 11 '14 at 19:54
  • docs.google.com/spreadsheet/… – pdrops Jan 14 '14 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 Jan 14 '14 at 22:53
  • Can't access the doc – Jacob Jan Tuinstra Feb 8 '14 at 20:22
up vote 1 down vote accepted

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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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