Date Reformatting in Excel – a solution to help fix incorrect date formats in Excel for when you use formulas
This week’s hint and tip is on date reformatting in Excel. Dates are used all the time in spreadsheets and often are inputted in different formats by the users. Sometimes this can cause an issue when you then need to use that date in a formula. We are going to go through a way in which you can correct the incorrect date format for when its then used later. We cover date formats and functions in our Advanced Excel training course but not this solution for correcting the format, so we decided to do a hint and tip on it. We are going to go through it now below.
Date Formats in Excel
In this blog we shall have a look at a common problem i.e. dates which have been formatted with a . instead of a /. Various companies we’ve come across during our work, download data into Excel from a foreign but compatible system to Excel. However the dates on their systems are sometimes of the format dd.mm.yyyy instead of dd/mm/yyyy.
This difference in format can cause problems when it comes to date calculations using functions in Excel such as MONTH, YEAR, DAYS, NETWORKDAYS.
In this blog and associated video we shall look at a nested IF statement which gets around this problem.
Example
We’ll go through the example in the spreadsheet and video below.
In the screenshot above cell A2 has a date 18.05.2022.
By using CONCATENATE we can join together with a / the DD and the MM and the YYYY parts of the date leaving out the .
The formula for this is: CONCATENATE(LEFT(A2,2),”/”,MID(A2,4,2),”/”,RIGHT(A2,4))
I.e. join together the first 2 characters (the day) followed by a / then the characters starting at position 4 but 2 long (the month) followed by another / and then the last 4 characters (the year).
To complete the nested statement we’ve added an IF statement testing for the . and if it is then do the CONCATENATE else leave the date as is.
NB. We assume the date in the false part of the IF statement is therefore correctly formatted already with a /
Take a look at the attached spreadsheet and video to help you understand more.
We look at Date functions in our advanced Excel course and more details can be found here on the website.
Worked example video
The video below shows you how you can use this feature in the spreadsheet attached here. (clicking here will download a copy to your computer so you can try it out!). We hope that you find the video useful and enjoy learning about it!
Take a look below at the video to find out more and then try it out on your own computer!
We hope you have enjoyed this hint and tip on copy paste macro in Excel. Why not take a look at our previous video hint and tip on using macros to copy and paste in Excel?