CODEWITHSUNDEEP
exceltfsvba
Ruan
Ruan

Reputation: 427

Excel/VBA: When cell changes, change another cell with different date format

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:

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

Is it possible to do it without VBA, if not how would I achieve this with 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.

Upvotes: 0

Views: 1162

Answers (3)

Ruan
Ruan

Reputation: 427

Excel\VBA : So what i did is:

'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!

Upvotes: 0

shash
shash

Reputation: 266

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

Use the formula in Cell 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

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'

Upvotes: 2

BruceWayne
BruceWayne

Reputation: 23283

You can use 

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

Formatted to yyyy/mm/dd.

Upvotes: 1

Related Questions