EXCEL SHEET auto enter Dates

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
87
Office Version
  1. 2016
Platform
  1. Windows
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

  • ScreenShot.png
    ScreenShot.png
    70.4 KB · Views: 32

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Not sure if this will help you, but CTRL+; will give you today's date as a static value.
 
Upvote 0
Thank you for your time to answer me , i just tried that , and it will open the insert pop dialog instead.
 
Upvote 0
That probably has to do with your country's formatting. (I detect that you are not from the USA ;))
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top