Excel incremental counter

QuestionEdit

How do you add a counter into a workbook that increments by date ( one per day ) regardless of whether the workbook / file is opened or not ? e.g. if you want to know how many days have past since your last visit. It will need to reference a cell with a date in it.  any suggestions ?

AnswerEdit

Yes exactly. If you are trying to do it by using worksheet function then it requires a cell reference where the last day of file opened date should be entered.

For example assume that you are entering the current date (By pressing Cntrl+Semicolon) in cell A1 before closing the workbook. Copy and paste the below formula in any of the cell other than A1 cell. The below formula will get you the Number of Days passed since your last visit based on the date which is entered in cell A1.

=IF(A1="","Enter The Date B4 Closing The File",TODAY()-A1&" Days left frm ur last visit")

But whenever you are closing the file you need to press Cntrl+Semicolon in A1 cell to input the (Current) Date on which the file was reviewed. Then onlywill calculate and give the number of days passed at the time of your next visit.

The above worksheet function requires a manual work that is entering the date in cell A1 at the time of closing the workbook. But the same can be done automatically by VBA.

Open yourworkbook and press Alt+F11 and press Cntrl+R which will get the Project Explorer window and do double click in [This workbook] and do copy and paste the below code into the right pane and press Alt + Q to close the VBE.

Private Sub Workbook_BeforeClose(Cancel As Boolean) Worksheets("Sheet1").Range("A1").Value = Format(Now(), "DD-MMM-YYYY") End Sub Private Sub Workbook_Open() Worksheets("Sheet1").Range("B1").Formula = (Date - Range("A1").Value) & " Days left frm ur last visit" End Sub

At the time of closing the workbook the above code will put the current date in cell A1. While opening the file the code will calculate the difference between the A1 cell that is last visit date with the current date and gives the Result in cell B1.

The below words may need to be altered based on your requirement.

"Sheet1" To "yoursheetname" Range("A1") To Range("Your desired Cell Reference where the Date should be stored") Range("B1") To Range("Your desired Cell Reference where the Result-DateDifference should be displayed")

In fact I don't know VBA (Macro), and the same is mentioned in my profile also. But I thought that I can give VBA solution for this, since it is not so complicated and very simple one. So just given a try and written the above code and this is the first time I am giving my own VBA solution. But I know that the code which I have given to you is not so simple and perfect because I am sure that the code can be shortened / simplified by VBA experts.

Hope I made it clear!