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 ,

 

Do you have rows without AM/PM that have dd/mm/YYYY and others that have mm/dd/yyyy? or do they all have the dd/mm/yyyy?

 

In the screen you present that is not legible.

 

Try this measure:

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.AfterDelimiter(Text.BeforeDelimiter([DAte], " ", 0), "/", 1)
     & "/"
     & Text.PadStart(Text.BeforeDelimiter([DAte], "/", 0), 2, "0")
     & " "
     & (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")
GuerauFF's avatar
GuerauFF
Helper II
6 years ago

It was way easier than that, I just had to swap two rows.

 

The final code is the following one: 

 

 

if Text.End([startDate], 2) = "AM" or Text.End([startDate], 2) = "PM" 
then Text.PadStart(Text.AfterDelimiter(Text.BeforeDelimiter([startDate], "/", 1), "/", 0), 2, "0") 
& "/" 
& Text.PadStart(Text.BeforeDelimiter([startDate], "/", 0), 2, "0") 
& "/" 
& Text.AfterDelimiter([startDate], "/", 1) 
else Text.PadStart(Text.BeforeDelimiter([startDate], "/", 0), 2, "0") 
& "/" 
& Text.PadStart(Text.AfterDelimiter(Text.BeforeDelimiter([startDate], "/", 1), "/", 0), 2, "0") 
& "/" & Text.AfterDelimiter(Text.BeforeDelimiter([startDate], " ", 0), "/", 1) 
& " " 
& (if Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter([startDate], " ", 0), ":", 0)) < 12 
then Text.BeforeDelimiter(Text.AfterDelimiter([startDate], " ", 0), ":", 0) 
else Number.ToText( Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter([startDate], " ", 0), ":", 0)) - 12 )) 
& ":" & Text.AfterDelimiter([startDate], ":", 0) 
& (if Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter([startDate], " ", 0), ":", 0)) < 12 
then " AM" else " PM"))

 

Thanks for all your help MFelix !