Solved

Date generator for it to show Days Left for due date.

Posted on 2016-09-27
11
61 Views
Last Modified: 2016-09-29
Hello guys, i have an alternative to make this possible by adding this code on the cell =IF(B6<>"",B6-TODAY(),"")

***But what i truly want is have a way where a code is created, so that i may attach it in the main page and it will calculate automatic date left from searching column "B" and display it in column "I".

again thanks for your time guys and help.

Date-Generator.png
0
Comment
Question by:Omar Hernandez
  • 6
  • 5
11 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 41820409
If you place that formula into a cell and double-click the lower right corner of the cell, it will autofill down.  That should give you what you want
0
 

Author Comment

by:Omar Hernandez
ID: 41820427
Hello there Aikimark, thanks for your response, i know but i am looking for another way of doing it. I have a code that i have pasted on the main page and i would like something similar like this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim eval As Long
If Target.Column = 2 Then
    eval = Cells(Target.Row, 2).Value - Date
    If eval > 0 Then
        If eval = 1 Then
            Cells(Target.Row, 10).Value = "Q"
            Cells(Target.Row, 10).Font.Color = -16776961
        ElseIf eval = 2 Then
            Cells(Target.Row, 10).Value = "QQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval = 3 Then
            Cells(Target.Row, 10).Value = "QQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval = 4 Then
            Cells(Target.Row, 10).Value = "QQQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval > 4 Then
            Cells(Target.Row, 10).Value = "QQQQQ"
            Cells(Target.Row, 10).Font.Color = -65536
        End If
        Cells(Target.Row, 10).Font.name = "Snap ITC"
        Cells(Target.Row, 10).Font.FontStyle = "Bold"
        Cells(Target.Row, 10).Font.Size = 8
    Else
        Cells(Target.Row, 10).Value = ""
    End If
    If Cells(Target.Row, 2) = Empty Then
        Cells(Target.Row, 10).Value = ""
    End If
End If

End Sub

Open in new window


a code which will do it automatically pasted on the main page, without having it on all the cell
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41821532
This would be better
Private Sub Worksheet_Change(ByVal Target As Range)
Dim eval As Long
If Target.Column = 2 Then
    If Cells(Target.Row, 2) = Empty Then
        Cells(Target.Row, 10).Value = ""
        Exit Sub
    End If
    eval = Cells(Target.Row, 2).Value - Date
    If eval > 0 Then
        If eval = 1 Then
            Cells(Target.Row, 10).Value = "Q"
            Cells(Target.Row, 10).Font.Color = -16776961
        ElseIf eval = 2 Then
            Cells(Target.Row, 10).Value = "QQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval = 3 Then
            Cells(Target.Row, 10).Value = "QQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval = 4 Then
            Cells(Target.Row, 10).Value = "QQQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval > 4 Then
            Cells(Target.Row, 10).Value = "QQQQQ"
            Cells(Target.Row, 10).Font.Color = -65536
        End If
        Cells(Target.Row, 10).Font.name = "Snap ITC"
        Cells(Target.Row, 10).Font.FontStyle = "Bold"
        Cells(Target.Row, 10).Font.Size = 8
    Else
        Cells(Target.Row, 10).Value = ""
    End If
End If

End Sub

Open in new window

0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Omar Hernandez
ID: 41821569
Thanks Akimark, for me to be more specific i want a code so that i may put on the main page( the same page where i have the one you have polished) where it calculate the date from "B" column and is input on the "I" column showing numbers.
***Those numbers = amount of days from now to the amount of days it will take, to get to the date it is display on column "B".
***(Example) on row, column "B" = 10/4/16 on column "I" it will = 5)
***(Example) on row, column "B" = 10/9/16 on column "I" it will = 10)
***(Example) on row, column "B" = 11/4/16 on column "I" it will =36 )

Hope this is clear friend.
0
 

Author Comment

by:Omar Hernandez
ID: 41821585
File is on this attachment
BK-TO-DO-LIST.xlsm
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 41821651
Please test this
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim eval As Long
    Dim colors As Variant
    colors = Array(0, -16776961, 26367, 26367, 26367, -65536)
    If Target.Column = 2 Then
        If Cells(Target.Row, 2).Value = Empty Then
            Cells(Target.Row, 10).Value = ""
            Exit Sub
        End If
        If IsDate(Cells(Target.Row, 2).Value) Then
            eval = Cells(Target.Row, 2).Value - Date
            Select Case eval
                Case 0
                    Cells(Target.Row, 10).Value = ""
                Case 1 To 4
                    Cells(Target.Row, 10).Value = String(eval, "Q")
                    Cells(Target.Row, 10).Font.Color = colors(eval)
                Case Is > 4
                    Cells(Target.Row, 10).Value = String(5, "Q")
                    Cells(Target.Row, 10).Font.Color = colors(5)
            End Select
            With Cells(Target.Row, 10).Font
                .name = "Snap ITC"
                .FontStyle = "Bold"
                .Size = 8
            End With
            Cells(Target.Row, 9).Value = eval
        End If
    End If

End Sub

Open in new window

0
 

Author Closing Comment

by:Omar Hernandez
ID: 41821678
thanks a mil aikimark, works like a charm... :)
0
 

Author Comment

by:Omar Hernandez
ID: 41821699
Hey there aikimark, quick question is it possible to have this code to work in a way, that when i click on the refresh button the code will re-check all the dates on the "B" column and re-arrange dates left on the "I" column ?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41821727
Possibly.  What do you mean by "rearrange"?
0
 

Author Comment

by:Omar Hernandez
ID: 41821819
Hey aikimark i have made a video for a better visual description if you may, please check it out on the link provided.

http://www.screencast.com/users/Omarantonio1231/folders/Refresh%20Dates/media/f19fa1e9-3805-40f9-8bb7-ced979e14608
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41822050
I think it should be possible.  However, this is a closed question.  Please post a new question about this new requirement.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question