Solved

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

Posted on 2016-09-27
11
57 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

773 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