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

[prabhatnath]

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)

Id	StartTime	EndTime
1	20/12/2023 10:33	20/12/2023 10:43
2	20/12/2023 11:10	20/12/2023 11:19
3	23/01/2024 13:32	23/01/2024 13:34
4	23/01/2024 13:37	23/01/2024 13:39
5	23/01/2024 13:47	23/01/2024 13:55
6	1/25/2024 10:03 AM	1/25/2024 10:17 AM
7	1/25/2024 10:23 AM	1/25/2024 11:02 AM
8	1/25/2024 11:17 AM	1/25/2024 11:25 AM
9	1/25/2024 9:04 AM	1/25/2024 9:31 AM

Thanks,

Prabhat

[Anonymous]

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]

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 · Accepted solution]

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.

[PijushRoy]

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]

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