Forum Discussion

samza18's avatar
samza18
Copper Contributor
Mar 15, 2024

How to standardize different time formats

Hi all,   Stuck in a dilemma, unfortunately a report is sent to me with a a date column that has all kind of different formats. Here are the 3 types: Is there any formula or script that could stan...
excel
Formulas and Functions
Macros and VBA
Office Scripts
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Mar 15, 2024

samza18 

To standardize the different date formats in Excel, you can use a combination of text manipulation functions such as TEXT, DATEVALUE, and LEFT, along with some logic to handle the different formats.

Assuming your dates are in column A, you can use the following formula in another column (let's say column B) to convert the dates to a standardized format (mm/yyyy):

=IF(ISNUMBER(DATEVALUE(A1)), TEXT(DATEVALUE(A1), "mm/yyyy"), IF(ISNUMBER(DATEVALUE(LEFT(A1, FIND("/", A1)-1 & "/" & MID(A1, FIND("/", A1)+1, FIND("/", A1, FIND("/", A1)+1)-FIND("/", A1)-1) & "/" & RIGHT(A1, LEN(A1)-FIND("/", A1, FIND("/", A1)+1))), "mm/yyyy"), TEXT(DATEVALUE(LEFT(A1, FIND("/", A1)-1 & "/" & MID(A1, FIND("/", A1)+1, FIND("/", A1, FIND("/", A1)+1)-FIND("/", A1)-1) & "/" & RIGHT(A1, LEN(A1)-FIND("/", A1, FIND("/", A1)+1))), "mm/yyyy"), TEXT(DATEVALUE(MID(A1, FIND("/", A1)+1, FIND("/", A1, FIND("/", A1)+1)-FIND("/", A1)-1) & "/" & LEFT(A1, FIND("/", A1)-1 & "/" & RIGHT(A1, LEN(A1)-FIND("/", A1, FIND("/", A1)+1))), "mm/yyyy")))

This formula checks for three conditions:

  1. If the value in cell A1 is already a valid date (using DATEVALUE), it converts it to the desired format using TEXT.
  2. If the date is in the format "dd/mm/yy", it rearranges the parts of the date and converts it to "mm/yyyy".
  3. If the date is in the format "mm/dd/yy" (with or without time), it rearranges the parts of the date and converts it to "mm/yyyy".

This formula should help standardize the different date formats in your Excel sheet, allowing you to perform calculations accurately in Power BI or any other analysis tool. You can drag this formula down to apply it to the entire column if needed. The text was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

Resources