• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I convert DD/MM/YY and time to MM/DD/YYYY and time from 12 hour format to 24 hour format

Akilan V
Did Your 'Yes' mean that You tested all those or what?
If You tested, then You should find needed solution to do it.
As well as You would notice
that some of Your used PCs settings would effect to needed solution,
which would make challenge to give exact reply ... especially, if You would like to use the result as date.
 
The bad news is that your dates are already in the (crazy) US format! It just happens that the date is 6-Feb which may well be wrong; are you expecting the 6-Jun? The problem arises where dates are read into Excel as text strings. If the machine localisation does not correspond to the convention used for the text version, a mess ensues. Where Excel has recognised a date, it can be corrected using the formula
=DATE(YEAR(Punch_time),DAY(Punch_time),MONTH(Punch_time))
Converting the text (23-Jun will fail to convert) is more of an issue.

I would recommend re-reading the original data source using Power Query and convert to DateTime using the localisation appropriate to the data.
 
Y
The bad news is that your dates are already in the (crazy) US format! It just happens that the date is 6-Feb which may well be wrong; are you expecting the 6-Jun? The problem arises where dates are read into Excel as text strings. If the machine localisation does not correspond to the convention used for the text version, a mess ensues. Where Excel has recognised a date, it can be corrected using the formula
=DATE(YEAR(Punch_time),DAY(Punch_time),MONTH(Punch_time))
Converting the text (23-Jun will fail to convert) is more of an issue.

I would recommend re-reading the original data source using Power Query and convert to DateTime using the localisation appropriate to the data.
yes, the date is 02 June. how do i go about
 
Akilan V
Did You tested and checked those search results (#2 Reply)?
Could You use format dd-mmm-yyyy instead to 'mix' days and months?
 
As I see it the damage has already been done at the point where Excel has interpreted dates input as text and converted them to numbers using the wrong date convention. It is possible to sort the mess after it has arisen but my preference would be to read the dates as text and control the way they are interpreted.

The questions then are: 1. "Do you have the dates listed as original format text strings?";
2. "Are you using a version of Excel that includes Power Query?"

In the attached file, I have read the date stored as (ambiguous) text twice using Power Query. I have converted the text to dates, once using English (World) and the second time English (US). The final table is Excel's conversion and depends upon your computer settings.
 

Attachments

  • DateTextPQ (PB).xlsx
    20 KB · Views: 2
Thank you. Unfortunately, I was not able to solve. talking to my people in data source to get in right format.
 
Back
Top