Todays date, future date and countdown

[IRC1968]

Hi,

This is my first post here so I apologize if I have broken any rules or protocols in any of the structure of wording of this first request, if I have please let me know for future reference and don’t just ban me like some other so called help forum did.

I am hoping that someone could help me with setting this up on Excel, I am creating a tracker to record, incoming, latest date of reply needed and actual date of reply.

So I have column J= received, Column K = need to reply by, Column L = replied on

What I want in J is today’s date which is easy enough with ctrl+; is there a way of having it set as dormant so that as soon as the cell is clicked on that date is activated and until then it remains empty?

What I have in K is J#+28 this adds 28 days to the date entered in column J, again is there any way to have the code dormant so that this automatically activates when cells in column J are active and not until then, as if I enter that code in the cell and nothing is showing in cell J it gives a date of the 28/01/2008.

In L I have tried a couple of codes and they have not worked so what I would like is a code that when J and K are active it generates a countdown for days left to reply and if this countdown reaches zero then the whole line is flagged up in red as late.

Lastly, is there a code that I can use that will automatically correspond with the J, K or L columns and match them with the same number cell in the other columns, sorry this isn’t so easy to explain but what I have to do at the moment is type in K is =J1+20 , J2+20, J3+20 and so on rather than just typing in J……+20 for column K to know it needs to associate itself with the same number cell in column A regardless what number it is.

To summarize,

J8 Received, (click on J8 and today’s date appears)
K8 reply by 20 working days, (when J8 is active K8 automatically generates a date 20 working days in advance)
L8 response sent, (when K8 is active L8 starts a countdown of days to go until K8 when this countdown hits zero the word LATE appears in A8 and the whole of the text in line 8 turns red, when a response is sent L8 locks in the date which it was sent)

This then duplicates on M,N & O8, P,Q & R8, S,T &U8 and again on V,W & X8 and then repeats on all lines below 8

Thanks in advance.

If someone knows a solution fr this and if it is allowed then a quick sample file in a private message would be very helpful.

[Richard Buttrey]

Hi,

This is my first post here so I apologize if I have broken any rules or protocols in any of the structure of wording of this first request, if I have please let me know for future reference and don’t just ban me like some other so called help forum did.

I am hoping that someone could help me with setting this up on Excel, I am creating a tracker to record, incoming, latest date of reply needed and actual date of reply.

So I have column J= received, Column K = need to reply by, Column L = replied on

What I want in J is today’s date which is easy enough with ctrl+; is there a way of having it set as dormant so that as soon as the cell is clicked on that date is activated and until then it remains empty?

What I have in K is J#+28 this adds 28 days to the date entered in column J, again is there any way to have the code dormant so that this automatically activates when cells in column J are active and not until then, as if I enter that code in the cell and nothing is showing in cell J it gives a date of the 28/01/2008.

In L I have tried a couple of codes and they have not worked so what I would like is a code that when J and K are active it generates a countdown for days left to reply and if this countdown reaches zero then the whole line is flagged up in red as late.

Lastly, is there a code that I can use that will automatically correspond with the J, K or L columns and match them with the same number cell in the other columns, sorry this isn’t so easy to explain but what I have to do at the moment is type in K is =J1+20 , J2+20, J3+20 and so on rather than just typing in J……+20 for column K to know it needs to associate itself with the same number cell in column A regardless what number it is.

To summarize,

J8 Received, (click on J8 and today’s date appears)
K8 reply by 20 working days, (when J8 is active K8 automatically generates a date 20 working days in advance)
L8 response sent, (when K8 is active L8 starts a countdown of days to go until K8 when this countdown hits zero the word LATE appears in A8 and the whole of the text in line 8 turns red, when a response is sent L8 locks in the date which it was sent)

This then duplicates on M,N & O8, P,Q & R8, S,T &U8 and again on V,W & X8 and then repeats on all lines below 8

Thanks in advance.

If someone knows a solution fr this and if it is allowed then a quick sample file in a private message would be very helpful.

Hi,
You can get Excel to respond to changes in cells by using Visual Basic, and having a bit of code that reacts to the Sheets 'SelectionChange' event. However you may not want to get into VBA.

The other solution is probably to use conditional formatting and set the font colour to the same as the sheet background until the condition is met. In the case of say K10, that condition would be =IF(J10<>"")

Can you zip up and attach the workbook here so we can take a look. I don't think you can attach to private messages.

Rgds

[shg]

Richard, does quoting the OP's entire post serve a purpose?

[Richard Buttrey]

Richard, does quoting the OP's entire post serve a purpose?

Hi,

It certainly helps me when I'm responding, since I often want to refer back to what has been said. Some posts are not that easy to understand, even when quoted in full, editing out most of the lines would just make understanding harder. I prefer to have it right in front of me rather than in another window/tab on the browser.

In any case I've always thought it was good netiquette to quote the original post. Certainly that seems to be the standard on the Usenet news groups.

However if you want me to refrain, then just say so.

Regards,

Richard

[shg]

I think it makes sense when there have been several intervening posts, especially to make clear what you're responding to. Otherwise, it just occupies space.

Thank you.

[IRC1968]

Hi Richard,

Thanks for the reply, I have changed it slightly now to include an additional column to include a separate countdown, the codes I am using are:

J8 ctrl+;
K8 =J8+28
L8 =K8-TODAY()
M8 ctrl+;

J8 is entered on the day of receipt
K8 is entered on the same day as J8
L8 is entered on the same day as J8
M8 is entered on the day of reply

This seems to do pretty much what I wanted it to do except for the fact that it is all a manual process rather than automatic or dormant within the cells, and the countdown cell doesn't action any changes when it hits zero which would be nice if it did.

Could you explain in simple terms what that bit of code =IF(J10<>"")will do and is that complete or is there something missing from between the <>'s and the ""'s.

If you could reply in the same way I have explained it in here as in, this goes in this cell, this in this cell and so on that would be great as I am at a VERY basic level in working out these codes and formula's.

Thanks in advance and thanks again for your first reply.