EXCEL SHEET auto enter Dates

[bobby786]

Hi All ,

I am came to this forum after a long time , this is the great platform and with awesome people , where stranger of no where will help each other.
I am father of 3 , and my JOB requires me to data entry on daily basis. Let me explain the situation .

I have a daily work sheet on excel , which have formulas and all to maintain the bank ledger and payments inn and out. There is a situation when i have to make the request of payment and i have enter the date on which i request the payment , and it will get approved like 2-3 days later , and when its approved ( notify via a TEXT message on whats-app) i have to enter the " Approved " and Date on which day it was approved. I just want to make this automatic. So that i don't have to enter the date every time , secondly to avoid mistake , because sometimes on day it would be hundreds of entries and multiple times in a day.

My sheet right now have more than 3000 entries , but i have saved as sample and cut down the few only to make it easy to understand. And i uploaded HERE since the forum does not allow me to attach the excel file.However i have attached the screenshot along with post which was allowed ,
Inside the sheet i have mentioned exactly the scenario which will further explain and clarify it , in case if i still lack in explaining please do let me know , as English is not my native language , i am sorry for that.

i am very hopeful that i will get help from here and i can save time to get work off early and spent with my kids.

Thank you very much in advance.

Regards
Bobby

 

[bobby786]

Try this on a copy of your workbook:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Xit
    Application.EnableEvents = False
    If Not Intersect(Target, Range("J:J")) Is Nothing And Target = "approved" Or Target = "Approved" Then
        Cells(Target.Row, 12) = Format(Now(), "yyyy.mm.dd")
    End If
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        If Cells(Target.Row, 3) <> "" Then
            Cells(Target.Row, 11) = Format(Now(), "yyyy.mm.dd")
        End If
    End If
Xit:
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo Xit
    Application.EnableEvents = False
    If Target.Count > 1 Then GoTo Xit
    If Not Intersect(Target, Range("A:A")) Is Nothing And Target = "" Then
        Target = Format(Now(), "yyyy.mm.dd")
    End If
Xit:
    Application.EnableEvents = True
End Sub

Right-click on the BILWANI sheet tab, View Code and paste the above. You will have to save the workbook as an Excel Macro-Enabled Workbook

@ Paul Ked

You are the man ! Thanks a lot , its working fine . Thank you very much

 

[bobby786]

That is correct, I haven't seen it.** I was simply explaining what BenMiller had suggested.

** BTW, you will generally get more potential helpers if you provide small sample data directly in your posts here. My signature block has help on that.
Many helpers will choose not to go to another site to download such a file and other are prevented from doing so by workplace security settings.

@Peter_SSs
okay mate i will read out that , thank you for information.

Regards

 

[Paul Ked]

You're welcome

 

[bobby786]

@ Paul Ked

I have used the sheet today during my work hours , but i am facing one situation i think i will need your help again for it , if i enter " Approved" in cell by mistake upon deleting the " approved" text from that cell the corresponding " Approved Date" column still have the date there. Is there any way to make it like when there is text " approved" in that cell of that particular column then the date appears , if i delete that text the date also get deleted and cell appear as empty.

I hope i explain it correct , if not please let me know i will make the screen recording .
Sorry for my poor english.

Regards
Bobby

 

[Paul Ked]

I'll have a look asap

 

[Paul Ked]

Replace your existing WorkSheet_Change code with this:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Xit
    Application.EnableEvents = False
    If Not Intersect(Target, Range("J:J")) Is Nothing And Target = "approved" Or Target = "Approved" Then
        Cells(Target.Row, 12) = Format(Now(), "yyyy.mm.dd")
    End If
    If Not Intersect(Target, Range("J:J")) Is Nothing And Target = "" Then
        Cells(Target.Row, 12) = ""
    End If
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        If Cells(Target.Row, 3) <> "" Then
            Cells(Target.Row, 11) = Format(Now(), "yyyy.mm.dd")
        End If
    End If
Xit:
    Application.EnableEvents = True
End Sub

 

[bobby786]

BINGO ... its working , Thank you Sir.

Regards

 

[Paul Ked]

 

[bobby786]

@Paul Ked
Yesterday i make one entry in Amount out as 24,000 RMB , but today for some reason i have to change it , and this kind of situation i do face 4-5 times a week where invoice amount changes so i have to change the amount , but when i change it the " Payment Request Date" also changes to today date, is it possible to make it like if there is changes in already filled data in amount out , it should ask me to keep the old date or change to current date ?

Regards

 

[Paul Ked]

Hi Bobby

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim yn As String
    On Error GoTo Xit
    Application.EnableEvents = False
    If Not Intersect(Target, Range("J:J")) Is Nothing And Target = "approved" Or Target = "Approved" Then
        Cells(Target.Row, 12) = Format(Now(), "yyyy.mm.dd")
    End If
    If Not Intersect(Target, Range("J:J")) Is Nothing And Target = "" Then
        Cells(Target.Row, 12) = ""
    End If
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        If Cells(Target.Row, 11) <> "" Then
            yn = MsgBox("Do you want to keep the date as " & Cells(Target.Row, 11) & "?", vbYesNo, "Date exists...")
        End If
        If Cells(Target.Row, 3) <> "" Then
            If yn = vbNo Then
                Cells(Target.Row, 11) = Format(Now(), "yyyy.mm.dd")
            End If
        End If
    End If
Xit:
    Application.EnableEvents = True
End Sub