Forum Discussion
Solved
Date format
Hi there! I am having some issues with date formatting... it's a nightmare! So, what I have is a column where I have both dates as "dd/mm/YYYY hh:mm:ss" and others as "dd/mm/YYYY hh:mm:ss AM/...
Hi GuerauFF ,
Try the following code:
if Text.End([DAte], 2) = "AM" or Text.End([DAte], 2) = "PM" then Text.PadStart(Text.AfterDelimiter(Text.BeforeDelimiter([DAte], "/", 1), "/", 0), 2, "0") & "/" & Text.PadStart(Text.BeforeDelimiter([DAte], "/", 0), 2, "0") & "/" & Text.AfterDelimiter([DAte], "/", 1) else Text.PadStart(Text.AfterDelimiter(Text.BeforeDelimiter([DAte], "/", 1), "/", 0), 2, "0") & "/" & Text.PadStart(Text.BeforeDelimiter([DAte], "/", 0), 2, "0") & "/" & Text.AfterDelimiter(Text.BeforeDelimiter([DAte], " ", 0), "/", 1) & " " & (if Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter([DAte], " ", 0), ":", 0)) < 12 then Text.BeforeDelimiter(Text.AfterDelimiter([DAte], " ", 0), ":", 0) else Number.ToText( Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter([DAte], " ", 0), ":", 0)) - 12 )) & ":" & Text.AfterDelimiter([DAte], ":", 0) & (if Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter([DAte], " ", 0), ":", 0)) < 12 then " AM" else " PM")
Hi MFelix
I tried it again and it's working, I guess I was making a mistake when putting the correct variable in the formula.
There's only one issue left, it seems like it's working for the dates with which I was having issues before, but it isn't working for the other ones that I had. Before changing the format:
And after the format change:
Any idea how I could make it work for both date types?
Thanks for your help!
Guerau