Forum Discussion

GuerauFF's avatar
GuerauFF
Helper II
6 years ago
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/...
General Comment
Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    6 years ago

    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")
MFelix's avatar
MFelix
Super User
6 years ago

Hi GuerauFF,

 

You issue is related with having different format for the months and day try to add the following column to the query and format as datetime:

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

DateTime.From ([DAte])

Should work as expected.

 

GuerauFF's avatar
GuerauFF
Helper II
6 years ago

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