Forum Discussion
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 standardize this? I only need the month and the year.
dd/mm/yy
mm/dd/yy time
mm/dd/yy
I've tried the left function but which some what works until it runs into caveats. The biggest issue is that when I'm doing calculations in PowerBI it's recognizing 8/4/2024 as August 4 2024 and not April 8 2024 so it's skewing my data but when the format is mm/dd/yyyy calculations are fine obviously.
Perhaps you have any flag which could say which day format is used in concrete report. Something in filename or in file header or like.
Since you mentioned Power BI I assume you use Power Query to load reports. Another option with it could be to check if there are any errors in column with dates after conversion. If they are to add another conversion step with another culture. However, that could work only if you definitely have in each report dates with day after 12th for the month.
Checking date by date won't work, 8/4/2024 always will be recognized as date in your regional format if not to instruct Excel we shall use another one.
Another thought is that it may be possible to address the problem by changing the workflow. If dates were not imported directly to Excel but, instead first taken into PowerQuery as text, the conversion to date can be done using the required source data locale. Loading to an Excel table should then be error free.
I think the data comes from a csv/txt file and you *opened* it in Excel. Never do that.
The csv-file has only one date format in it. But it is different from date format on your system.
Always *import* csv-files via Power Query. You have the option to change the data type - even with the proper locale if necessary.
This was developed on a non-US locale machine so may need some adaption.
ReverseDateλ = LAMBDA(date, //"Reverses day and month under the assumption that dates have been imported to Excel using an incorrect locale" LET( // "Convert Excel date back to text" textDate, IF(ISNUMBER(date), TEXT(date, "dd/mm/yyyy hh:mm"), date), // "Split text date in order to reverse day and month" splitDate, TEXTSPLIT(textDate,{"/","-","."," "}), reordered, CHOOSECOLS(splitDate, {2,1,3}), textTime, TEXTJOIN(" ",, DROP(splitDate, , 3)), // "Recombine and evaluate" return, VALUE(TEXTJOIN("/",,reordered) & " " & texttime), return ) )In use, one might have the worksheet formula
= MAP(dateList, ReverseDateλ)As I understood concrete report could be done in US, could be in non-US locale. Without additional information we can't decide based only on dates shall we revert them or not. Or first to check do we have any texts with columns with dates. If yes to revert and change all dates like 8/4/2024 from April on August.
If so in Power Query it could be much easier.
My preference would be to perform any necessary conversion in PQ rather than introduce erroneous conversions in Excel. Once one is at the point of having suspect dates on the sheet then, as you suggest, examining an individual date may not help. To have a reasonable chance of identifying conversion errors it is the whole dataset that should be examined. The presence of dates with days in the range 13th-31st suggests the import was correct whereas dates remaining as text should be a red flag.
My thought in writing the function is that is that, provided applying it converts an entire dataset to numbers, the resulting dates are most likely valid.
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:
- If the value in cell A1 is already a valid date (using DATEVALUE), it converts it to the desired format using TEXT.
- If the date is in the format "dd/mm/yy", it rearranges the parts of the date and converts it to "mm/yyyy".
- 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.
