Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

Date Generator For it to show Days Left for due date

Hello Experts Exchange Community, trying to create a code to do the following.

Search:
search for today date.

Then calculate how many days is left for each rows, that has dates on the "B" column.

Have it display on "G" column

Thanks guys for the help and your time.

Date-Generator.png
0
Omar Hernandez
Asked:
Omar Hernandez
  • 3
  • 2
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Assuming your date is in B6, then try this...

In G6
=IF(B6<>"",B6-TODAY(),"")

Open in new window

and then format the formula cell as General.
0
 
Omar HernandezAuthor Commented:
Hey there Neeraj this code works great, anyway i can add it to this macro so that anytime i add this function this code will also be added on the I Collumn. I tried the following, but i had no luck.

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("GENERAL")

If ActiveCell.EntireRow.Cells(1, 5) <> "" Then Label3.Caption = ActiveCell.EntireRow.Cells(1, 5)
    If ActiveCell.EntireRow.Cells(1, 4) <> "" Then Label4.Caption = ActiveCell.EntireRow.Cells(1, 4)
     If ActiveCell.EntireRow.Cells(1, 6) <> "" Then Label5.Caption = ActiveCell.EntireRow.Cells(1, 6)

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for a Name number
If Trim(Me.textbox_name.Value) = "" Then
Me.textbox_name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If

'copy the data to the database
        ws.Cells(iRow, 3).Value = "          " & Me.textbox_name.Value
        ws.Cells(iRow, 4).Value = Me.Label4.Caption
        ws.Cells(iRow, 5).Value = Me.Label3.Caption
        ws.Cells(iRow, 6).Value = Me.Label5.Caption
        ws.Cells(iRow, 7).Value = "2"
        ws.cells(irow, 9).value = IF(B7<>"",B7-TODAY(),"")


'clear the data
Me.textbox_name.Value = ""
Me.textbox_name.SetFocus
End Sub
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe like this...
ws.Cells(iRow, 9).Formula = "=IF(B" & iRow & "<>"""",B" & iRow & "-TODAY(),"""")"
ws.Cells(iRow, 9).NumberFormat = "General"

Open in new window

0
 
Omar HernandezAuthor Commented:
Better than what i have expected thanks a mil Neeraj
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Omar! Glad to help.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now