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 ,

 

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")
GuerauFF's avatar
GuerauFF
Helper II
6 years ago

Hey MFelix !

 

I swear it will be the last issue. This morning when I ran the formula I thought it was working properly and I moved to some other tasks, but now, I see that there's still one issue.

 

In the rows that initially have this format: dd/mm/yyyy hh:mm:ss, from the formula I am obtaining this format: mm/dd/yyyy hh:mm:ss AM/PM and I should be obtaining dd/mm/yyyy hh:mm:ss AM/PM. I guess I just have to change the order of some of the rows in the formula, not sure which ones though...

 

How would it be the formula considering this small change?

 

Thanks!

 

EDIT!!! Nevermind, I got it on my own. Thanks anyway!

  • 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 !