A few years ago I wrote an article on extracting the end of quarter date from a date. I recently had a query that was related and I tweaked the previous solution to solve it.
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.
Related Posts
Indian Financial Year Month Number in Excel
The Indian Financial Year start on 1 April. Like Australia its Financial Year month numbers can be painful. Here is a formula to sort them out.
Retrofit a Factor to an Excel Budget
Range name technique
It is common to have a Factor in a cell or cells in a budget to allow you to easily tweak the numbers by a percentage. If you want to add a Factor to an existing budget model here is how you can do it.
Feedback
With my hair style I can relate to this comment.
Sometimes you need a conditional AVERAGE
AVERAGEIF to the rescue
There are times when estimating numbers that an average is a good message to use.
Filter by Cells Value Excel Hack
Excel has a right click Filter option that speeds up filtering by a single value. You can hack that shortcut to do a little bit more.
Mouse Based Formulas in Excel
Are you a man or a mouse?
Do you hate using the keyboard for formulas? Does having to peck the = or + key to create a formula annoy you? Well there is an answer.
New Formula Symbol in Excel
The @ is re-purposed and the # is new
With introduction of Dynamic Arrays in Office 365 Excel has one new formula symbol and another that was previously only use in formatted tables.
Excel Border Icons
Speed up your work
I typically turn gridlines off on my sheets and then use borders for the lines. I have added icons to my Quick Access Toolbar to speed up the process.
Reading Books Online – legally
CPA Australia option
Seeing we may be stuck at home for a while here is a suggestion regarding reading books online. If you are a member of CPA Australia you can read certain books online for free.
New Dynamic Array Function SEQUENCE
Part One - its all about spilling
Dynamic arrays have the potential to change the way Excel spreadsheets are created. They were released in the January 2020 wave of updates to the Office 365 subscription version.
Issue with extracting a sheet name in Excel
Single sheet file issue
I wrote about a formula to extract the sheet name many years ago, but I just found an issue with it. Click here to see older blog post.
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.
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.
Negative Time in Excel
Party like its 1904
It is possible, but it involves a setting change that comes with a warning.
Weeks and days calculation in Excel
In Excel we tend to work in years, months or days. There may be times when you want to work in weeks.
Getting Wild About XLOOKUP
Part Four - you can use wildcards!
Being able to use wildcard characters like * and ? in searches is something we are used to having. Now XLOOKUP allows you to do those types of searches in Excel.
Excel XLOOKUP Actually Looks Down
Part Three - but things are looking up
Did you know that VLOOKUP actually looks down? That’s right, it finds the first entry from the top down. The default XLOOKUP also looks down, but can start from the bottom and look up.
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.
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
Excel XLOOKUP Flexibility
Part two
The INDEX and OFFSET functions can return a reference to a cell, something VLOOKUP can’t do. XLOOKUP can return a cell reference, let’s see one way to use it.