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

 

[BenMiller]

Not sure if this will help you, but CTRL+; will give you today's date as a static value.

 

[bobby786]

Thank you for your time to answer me , i just tried that , and it will open the insert pop dialog instead.

 

[BenMiller]

That probably has to do with your country's formatting. (I detect that you are not from the USA )

 

[Peter_SSs]

i just tried that , and it will open the insert pop dialog instead.

I suspect that you may not be familiar with the notation
CTRL+; means to hold down the Ctrl key and then press the semicolon key. You do not press the + key.

 

[Paul Ked]

Hi there. When you say

When there is Entry in Amount out Column with conditions that related to column also have filled in data

on your spreadsheet, what do you mean by the other conditions? ie. Which columns have to be filled before the date can be entered in?

 

[bobby786]

Hi there. When you say on your spreadsheet, what do you mean by the other conditions? ie. Which columns have to be filled before the date can be entered in?

Dear Paul ,
Yes exactly that's what i meant, because i will be putting the date in approved date column only when the amount and "related to" column already filled with data , this practice to avoid the fill dates which has incomplete data.

 

[bobby786]

I suspect that you may not be familiar with the notation
CTRL+; means to hold down the Ctrl key and then press the semicolon key. You do not press the + key.

Dear Peter_SSs

i am sorry , yes i am not very professional in excel , i am trying to learn more , and yout tips is working as you described , but it does not serve my purpose , i beleive you have not seen yet the spreadsheet i share where i mentioned automation of these thing , by doing CTRL+ , i still have to goto that cell and press key , which requires the time although less then manually inserting the date.
If you have chance to look at my spreadsheet i think things will be more clear. Or Maybey my explanation is too bad , sorry for it.

Regards

 

[Paul Ked]

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

 

[Peter_SSs]

i beleive you have not seen yet the spreadsheet i share

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.