A1 = formula

A2:(infinite) Date

I want starting from A2 when I type in a date, the newest substrates the oldest. When a date is added, it becomes the newest date.

I know A3-A2 can return the sum of days but in this case it involves the use of table in 2010. If I do the subtraction, when I add a new date at the bottom of the table, the date is not counted.



Is there any way to make it done?

Hi
Can you post a sample of your spreadsheet and I can take a look for you.
Tony (OTPM)

Like the last question I got help from you:
https://www.msofficeforums.com/excel...me-column.html

paste these macros in a module (change worksheet name as needed.)

Sub auto_open()
' works when you double click a cell and press enter, not when formula result changes, or when you copy formula to range
' whenever you need to update , double click on any cell then press enter
ThisWorkbook.Worksheets("Foaie2").OnEntry = "datesubs"

End Sub

Sub datesubs()
Dim lastrow As Long

With Sheets("Foaie2")
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Cells(1, 1).Value = Cells(lastrow, 1).Value - Cells(2, 1).Value

End With
End Sub

http://www.youtube.com/watch?v=kv2WX3KB0Z0

More on my question:
if you cannot understand my text description, please watch this video.

there is no video..

sorry, there is .. but even the name of the video says something else than your original post does (How to subtract dates from cell to cell in a table in Excel 2010)
Please save my time and try to be more explicit...

Yeah... sorry... my last post was misleading...
But thanks for your macros.
I'm looking deep at it now and try to figure out this.

ok, look at this line, which does all the job:
Cells(1, 1).Value = Cells(lastrow, 1).Value - Cells(2, 1).Value
maybe you can use this instead :
Cells(1, 1).Value = Cells(lastrow, 1).Value - Cells(lastrow-1, 1).Value
it subtracts the last value from the previous...

I can get the number of days when I click run the macro datesubs, not auto_open.
When I click auto_open, nothing happens.

But when I run those two macros, I double click any cells and press "enter". It shows the error message:

"Cannot run the macro [my xlsm file location]. The macro may not be available in this workbook or all macros may be disabled."

here is a sample attached...
Make sure you have macro security set to "disable all macros, with notification"
and when you open this workbook, in the message bar click options-enable this macro

Attached Files

test.xlsm (17.1 KB, 12 views)

Yes, thank you very much for ya file.
The only thing that goes wrong is that when I expand the table (I set the A column a table) but for whatever reasons I didn't enter a new date. So the lastrow is empty, Excel asks me to debug the line:

can you attach a sample of your worksheet?

My Fiance.xlsm

The sample data is just meaningless, nothing personal.
The error occurs when
Ya leave the last row blank and double click any cell and press ENTER.

maybe i am missing something, the macro works ...and is searching for the last row in column A which is not empty and subtracts it from the cell A2.
use an macro error handler in your macro: (you may copy on your code the red line)
this error handler just ignores errors, but if there are still problems, we will use another one, if necessary...


Sub datesubs()
Dim lastrow As Long
Please keep in mind that the code works only when you enter data in any cell in this worksheet, not when you copy cells or expanding tables

With Sheets("Sheet1")
On Error Resume Next
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Cells(2, 1).Value = Cells(lastrow, 1).Value - Cells(4, 1).Value

End With
End Sub