Fix dd.mm.yy date format

On a recent Webinar I was asked a question about an unusual date structure that was imported. The structure dd.mm.yy was not recognised by Excel as a date. Here is formula that fixes it.

Below is an example of the date issue.

The formula in cell B2 is

=SUBSTITUTE(A2,".","/")*1

As you can see the dates in column A are left aligned. That is a clue that they are not recognised as dates in Excel. Dates are right aligned.

The SUBSTITUTE function replaces the full stop between the numerals with a / and makes it look like a date.

This isn’t sufficient as the SUBSTITUTE function will return text. The *1 at the end converts the text date in to a real date that Excel recognises.

Note: Power Query can also automatically fix dates like these when it imports data.

 

 

 

Free Webinar Recording – Excel Format as Table Feature

Feedback score 95% based on 58 responses

In March 2020 I presented a session on Excel’s Format as Table Feature. I covered it’s advantages and how to use it to improve your Excel files.

The detailed pdf manual and example file can be downloaded using the button below. Content is listed below the video.

Download Materials

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

In this session you will learn all about Excel’s formatted tables.

Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.

Formatted tables can allow you to create powerful reports like those in a relational databases.

Topics covered

  • advantages and limitations of formatted tables
  • keyboard shortcuts
  • using formatted tables with formulas
  • solutions to some of the limitations of formatted tables
  • using range names with formatted tables
  • using formatted tables with data validations
  • creating a running total
  • using PivotTables
  • Relationships (Data tab)

As always I will share a few other tips.

Free Webinar Recording – Excel Budget Challenge Solution

Feedback score 92% based on 83 responses

In February 2020 I reviewed my solution to an Excel Budget Challenge from late 2018. This included some advanced Excel budget techniques.

The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video.

Download Materials

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

This session includes a lot of useful content and it brings together a number of different techniques. The content is worthy of a paid session but since it was a public challenge I am presenting the session for free.

The materials includes the challenge documentation, the pdf manual and the solution file. I assume you have read the documentation. You also receive the blank Excel file if you want to create your own solution.

Techniques/topics covered include

  • using INDEX-MATCH (better alternative to VLOOKUP)
  • 3-D formulas to summarise all report sheets and techniques to make using them easy
  • using a reporting template to speed up creation
  • validations
  • extracting sheet names
  • automating reports