Excel：单元格变更时自动转换TFS长日期为短日期（支持新增行）

Hey there! Let’s break down solutions for your date formatting problem—whether you want to avoid VBA entirely or need a macro-based approach, we’ve got you covered for both new rows and weekly TFS data updates.

Option 1: No VBA Required (Recommended for Most Scenarios) #

Great news: you absolutely can solve this without writing any code! The trick is using Excel Tables to auto-handle new rows, paired with a simple formula that updates automatically when TFS refreshes your data.

1. Convert your data to an Excel Table

   • Select your full data range (including headers) and press Ctrl+T. Check the box for "My table has headers" and click OK.
   • Tables automatically expand when new rows are added (either manually or via TFS imports), so your formatting logic will carry over to new data without extra work.

2. Add the short date formula
   Let’s say your TFS long dates live in a column named Long Date (adjust this to match your actual header). In the adjacent column (name it Short Date for clarity), enter this formula in the first data row:

   =TEXT([@[Long Date]],"yyyy/mm/dd")
   

   If you want to keep the value as a true date (not plain text), use this formula instead, then format the cell as a short date:

   =INT([@[Long Date]])
   

   Right-click the cell → Format Cells → Date → pick the yyyy/mm/dd style.

3. Works seamlessly with TFS weekly updates
   Whenever TFS refreshes the long date values, the formula will recalculate automatically—your short dates will update instantly without any manual steps.

Pro tip: If TFS overwrites your data, just ensure the table structure stays intact (same column headers) and the formula will keep working as expected.

Option 2: VBA Solution (If Tables Aren’t Feasible) #

If you need more control or can’t use tables for some reason, a Worksheet Change event macro will handle auto-updates, new rows, and TFS refreshes seamlessly.

Step 1: Open the VBA Editor #

Right-click your worksheet tab (e.g., "Sheet1") and select View Code.

Step 2: Paste This Code #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim updatedCell As Range
    Dim dateColumn As Range
    
    ' Change "A" to your actual long date column (e.g., "C" for column C)
    Set dateColumn = Intersect(Target, Me.Columns("A"))
    
    If Not dateColumn Is Nothing Then
        ' Turn off events temporarily to avoid infinite loops
        Application.EnableEvents = False
        
        For Each updatedCell In dateColumn
            ' Only process cells with valid date values
            If IsDate(updatedCell.Value) Then
                ' Set adjacent column (B here) to short date format and value
                updatedCell.Offset(0, 1).NumberFormat = "yyyy/mm/dd"
                updatedCell.Offset(0, 1).Value = Int(updatedCell.Value)
            Else
                ' Clear the short date cell if the source isn't a valid date
                updatedCell.Offset(0, 1).ClearContents
            End If
        Next updatedCell
        
        ' Turn events back on
        Application.EnableEvents = True
    End If
End Sub

What This Code Does: #

• Auto-triggers on changes: Whenever a cell in your long date column is updated (from TFS refreshes or new rows), it instantly updates the adjacent cell with the short date.
• Handles new rows: Any new row added (manually or via TFS) will get the short date as soon as the long date is populated.
• TFS refresh-friendly: If TFS overwrites existing long dates, the macro will re-calculate the short dates right away.
• Cleanup: If a long date cell is cleared or has non-date data, the corresponding short date cell is emptied to avoid junk values.

Quick Setup Notes: #

• Save your file as an .xlsm (Macro-Enabled Workbook) or the macro won’t work the next time you open the file.
• Adjust column references: Change "A" to your actual long date column, and Offset(0,1) if you need the short date in a different adjacent column (e.g., Offset(0,-1) for the column to the left).

内容的提问来源于stack exchange，提问作者Ruan