auto date changes
auto date changes
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: auto date changes

  1. #1
    Guest

    Default

     
    I am using conditional formatting to change a cells color as it gets within 2 days of a due date, thanks to you providing the solution for another user. My question is:
    once the due date arrives, is there a way to make it change automatically to 30 days from that date?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I assume that you mean that 2 days before the due date your cell (I have used A4) turns yellow and 30 days after due date cell turns red. If so, put this formula in A1 =today() and use the following conditional formatting on cell A4 containing your due date:

    1. formula is =$A$1>=A4+30 format cell red
    2. cell value is less than or equal to =$A$1-2 format cell yellow

    Hope this helps
    regards
    Derek

  3. #3
    Guest

    Default

    Thanks for replying Derek.

    Actually I want the date to adjust automatically once it reaches the due date.
    For example, today the cell, which has a date of 3/12/02, turned red because it is 2 days before 3/12/02. On 3/12/02 I would like the date in that cell to automatically change to 4/11/02, 30 days away, my next due date.


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    You could use an event macro in your worksheet. Again this assumes your due date is in A4 and the formula in A1 is =today()

    Right mouse sheet tab, left mouse View Code, paste this in

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1").Value = Range("A4").Value Then Range("A4").Value = Range("A4").Value + 30
    End Sub

    I am not sure how you could do this if you have many cells with due dates.

    HTH
    regards
    Derek

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again
    Actually if you have several due dates (say in the range A4:A12) you could use this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    For Each cell In Range("A4:A12")
    If cell.Value = Range("A1").Value Then cell.Value = cell.Value + 30
    Next
    End Sub

    regards
    Derek

  6. #6
    Guest

    Default

    Hi Derek,

    In the cell with the date, all I have done is formatted that cell for dates, and used conditional formatting to alert me. I have been manually entering the new due date every 30 days. So I have not been using another cell with a formula. Would your solution still work under those conditions?
    Thanks for the help,
    Jon

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-10 17:04, Derek wrote:
    Hi again
    Actually if you have several due dates (say in the range A4:A12) you could use this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    For Each cell In Range("A4:A12")
    If cell.Value = Range("A1").Value Then cell.Value = cell.Value + 30
    Next
    End Sub

    regards
    Derek

    Hi Jon
    I assume in your conditional formatting you use a formula referring to Today() or Now().
    Just change the above code to:

    Private Sub Worksheet_Change(ByVal Target As Range)
    For Each cell In Range("A4:A12")
    If cell.Value = Date Then cell.Value = cell.Value + 30
    Next
    End Sub

    That should do it (you can test it by changing the due date in your cell to today's date)

    regards
    Derel


  8. #8
    Guest

    Default

      
    Thanks Derek, what you suggested worked!
    Jon

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com