Excel / VBA：当单元格更改时，更改具有不同日期格式的另一个单元格

I want to change an excel cell when another cell changes. MY data comes from TFS and the date it returns is a long date (2018/02/02 12:07:33 AM) but the column next to it needs to change to the short date (2018/02/02) There can be newly added rows at any time. Using the following works fine but as soon as a new row is added the short date is blank:

我希望在另一个单元格发生变化时更改excel单元格。我的数据来自TFS,它返回的日期是一个很长的日期(2018/02/02 12:07:33 AM),但它旁边的列需要更改为短日期(2018/02/02)可以有随时新添加的行。使用以下工作正常,但只要添加新行,短日期为空:

=DATE(YEAR(E3);MONTH(E3);DAY(E3))

Is it possible to do it without VBA, if not how would I achieve this with VBA.

是否可以在没有VBA的情况下完成,如果不是,我将如何使用VBA实现这一点。

|A1                      |A2               |
|Long Date               |Short Date       |
|2018/02/02  12:07:33 AM |2018/02/02       |

The list from TFS will change every week and the long dates may also change per day or per week. At first the long date will be empty, when a user presses a specific button then the query from TFS will update the long date cell.

TFS列表每周都会更改,长日期也可能每天或每周更改。首先,长日期将为空,当用户按下特定按钮时,来自TFS的查询将更新长日期单元格。

3 个解决方案

#1

If I got your question right, the solution seems to be quite simple.

如果我的问题正确,解决方案似乎很简单。

Use the formula in Cell A2: "=A1"

使用单元格A2中的公式:“= A1”

Right-click on the Cell A2 and select 'Format Cells...' Choose 'Custom' option in Category and then type in "yyyy/mm/dd" for the Type

右键单击Cell A2并选择'Format Cells ...'在Category中选择'Custom'选项,然后输入类型为“yyyy / mm / dd”的类型

That should be it. Since the cells are linked by formula, they should automatically update as long as the Calculation Options is set to 'Automatic'

那应该是它。由于单元格是按公式链接的,只要“计算选项”设置为“自动”,它们就会自动更新

#2

You can use

您可以使用

=DATE(YEAR(A1),MONTH(A1),DAY(A1))

Formatted to yyyy/mm/dd.

格式化为yyyy / mm / dd。

#3

Excel\VBA : So what i did is:

Excel \ VBA:所以我做的是:

'Add smalldate to all items in the items page
    Dim LastRow As Integer
    Dim RowNumber As String
    Dim ColumnNumber As String: ColumnNumber = "F"
    Dim FixedVariable As String: FixedVariable = "F3:"

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    RowNumber = ColumnNumber & LastRow

    Dim RangeValue As String: RangeValue = FixedVariable + RowNumber
    Range(RangeValue).Formula = "=DATE(YEAR(E3),MONTH(E3),DAY(E3))"

Works like a charm, I need to use better naming conventions!

像魅力一样,我需要使用更好的命名约定!

#1