Forum Discussion

prabhatnath's avatar
prabhatnath
Icon for Advocate III rankAdvocate III
2 years ago
Solved

Converting Text Column (Some record with AM/PM and some without) to DateTime Issue

HI I have imported a SharePoint List and facing issues to convert the column to DateTime as few records are with AM or PM but few does not have AM or PM at all.

 

The rows that are not having AM or PM are of dd/MM/yy hh:mm (24Hr) format but the one with AM or PM are in MM/dd/yyyy hh:mm tt format.

 

Please suggest what will be the best way to convert these columns to DateTime so that it work for future imports too?

 

Here is how the SP List looks like:

 

Here is how the Imported Data Looks like.

EDIT: (Added Sample Data)

IdStartTimeEndTime
120/12/2023 10:3320/12/2023 10:43
220/12/2023 11:1020/12/2023 11:19
323/01/2024 13:3223/01/2024 13:34
423/01/2024 13:3723/01/2024 13:39
523/01/2024 13:4723/01/2024 13:55
61/25/2024 10:03 AM1/25/2024 10:17 AM
71/25/2024 10:23 AM1/25/2024 11:02 AM
81/25/2024 11:17 AM1/25/2024 11:25 AM
91/25/2024 9:04 AM1/25/2024 9:31 AM

 

 

Thanks,

Prabhat

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi prabhatnath ,

     

    Modify formula like below:

    StandardizedStartTime1 =
VAR OriginalStartTime = Table1[StartTime]
VAR IsAM =
    CONTAINSSTRING ( OriginalStartTime, "AM" )
VAR IsPM =
    CONTAINSSTRING ( OriginalStartTime, "PM" )
VAR TimePart =
    TIMEVALUE ( FORMAT ( Table1[StartTime], "HH:mm" ) )
VAR HourPart =
    HOUR ( TIMEVALUE ( FORMAT ( TimePart, "HH:mm" ) ) )
RETURN
    IF (
        OR ( IsAM, IsPM ),
        OriginalStartTime,
        IF ( HourPart > 12, OriginalStartTime & " PM", OriginalStartTime & " AM" )
    )

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi prabhatnath 

    Please use LOCALE option in Power Query editor to fix the DateTime issue
    If needed, You can remove word AM/PM by using REPLACE (replace the AM or PM as blank) before using LOCALE function 
    https://www.myonlinetraininghub.com/change-type-using-locale-with-power-query

     


    If your requirement is solved, please make sure to MARK AS SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

    Thanks
    Pijush
    www.MyAccountingTricks.com 
    https://www.youtube.com/MyAccountingTricks

     

    • prabhatnath's avatar
      prabhatnath
      Icon for Advocate III rankAdvocate III
      2 years ago

      If we see the rows that are not having AM or PM are of dd/MM/yy hh:mm (24Hr) format but the one with AM or PM are in different format like MM/dd/yyyy hh:mm tt.

       

      I have added sampel data to the main question post for easy.

       

      Also I get erro like culture not supported, so not sure what is the current culture used to save this.

       

      LOCALE option in Power Query editor did not help

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi prabhatnath ,

     

    You can create below formula:

     

    start_ = 
IF (
    CONTAINSSTRING ( Table1[StartTime], "AM" ),
    Table1[StartTime],
    IF(CONTAINSSTRING ( Table1[StartTime], "PM" ),
    Table1[StartTime],
    Table1[StartTime] & " " & "AM"
))

     

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • prabhatnath's avatar
      prabhatnath
      Icon for Advocate III rankAdvocate III
      2 years ago

      Thank you Anonymous this will help add AM to the missed ones but if you see the hours in the missed one are in 24Hr format so appending AM may not help. As we cant convert the new column to DateTime type. Any suggestion here?

      Thanks,

      Prabhat

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi prabhatnath ,

         

        Modify formula like below:

        StandardizedStartTime1 =
VAR OriginalStartTime = Table1[StartTime]
VAR IsAM =
    CONTAINSSTRING ( OriginalStartTime, "AM" )
VAR IsPM =
    CONTAINSSTRING ( OriginalStartTime, "PM" )
VAR TimePart =
    TIMEVALUE ( FORMAT ( Table1[StartTime], "HH:mm" ) )
VAR HourPart =
    HOUR ( TIMEVALUE ( FORMAT ( TimePart, "HH:mm" ) ) )
RETURN
    IF (
        OR ( IsAM, IsPM ),
        OriginalStartTime,
        IF ( HourPart > 12, OriginalStartTime & " PM", OriginalStartTime & " AM" )
    )

         

        Best Regards,
        Adamk Kong

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.