EXCEL SHEET auto enter Dates

bobby786

New Member
Joined
Apr 24, 2014
Messages
38
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
 

Attachments

BenMiller

Board RegularThe ONLY cool kid on the block
Joined
Nov 17, 2011
Messages
1,947
Office Version
365
Platform
Windows
Not sure if this will help you, but CTRL+; will give you today's date as a static value.
 

bobby786

New Member
Joined
Apr 24, 2014
Messages
38
Thank you for your time to answer me , i just tried that , and it will open the insert pop dialog instead.
 

BenMiller

Board RegularThe ONLY cool kid on the block
Joined
Nov 17, 2011
Messages
1,947
Office Version
365
Platform
Windows
That probably has to do with your country's formatting. (I detect that you are not from the USA ;))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,790
Office Version
365
Platform
Windows
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

Active Member
Joined
Jun 4, 2015
Messages
440
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

New Member
Joined
Apr 24, 2014
Messages
38
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

New Member
Joined
Apr 24, 2014
Messages
38
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

Active Member
Joined
Jun 4, 2015
Messages
440
Try this on a copy of your workbook:
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,790
Office Version
365
Platform
Windows
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. :)
 

Forum statistics

Threads
1,078,252
Messages
5,339,097
Members
399,277
Latest member
Jyoti C

Some videos you may like

This Week's Hot Topics

Top