title: Excel/VBA: When cell changes, change another cell with different date format
body: <p>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 (<code>2018/02/02  12:07:33 AM</code>) but the column next to it needs to change to the short date (<code>2018/02/02</code>)
  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:</p>
  
  <pre><code>=DATE(YEAR(E3);MONTH(E3);DAY(E3))
  </code></pre>
  
  <p>Is it possible to do it without VBA, if not how would I achieve this with VBA.</p>
  
  <pre><code>|A1                      |A2               |
  |Long Date               |Short Date       |
  |2018/02/02  12:07:33 AM |2018/02/02       |
  </code></pre>
  
  <p>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.</p>