Timestamp - MM/DD Swap

[Sartosvrenht]

Hey People,

I hope that everyone is doing great!

Would someone be able to help me a bit with this as well as send me some videos so that I may understand how to do this on my own.

So, the problem that I am having is that my Excel Table is inputting dates on the below mentioned format, though the formula on each specific cell is written as (example: 07-10-2021 23:01:04). Is there a formula I can use so that the cell displays as "10-07-2021 23:01:04" instead?

I searched the forum, though I was unable to find any answers!

Thanks in advance!

 

[Alex Blakenburg]

It looks like you are using Power Query.
1) What is your own region setting for date, is it dd-mm-yyyy ?
2) Where are you getting the data from ?

There are 2 places you can change it in Power Query
Option 1) On a column basis.
Click on the data type icon in the column heading and and select Using Locale at the bottom.
in the next screen select Date as the data type and where the file originated (but US should work based on the format)

Option 2) Change the PQ region setting
File > Options and Setting > Query Options > Regional Settings > Change to Locale (source of data) but US should work.
Note: this will impact all Queries in that workbook.

 

[Sartosvrenht]

The data is being imported from a Google Sheets document. Basically, I have the file I made in Excel 2019 and the information from the Timestamp is from a Google Check In Form that users use to check in to their Shifts. I copy the information imported from the Form to Google Sheets in my Excel file. As far as I know, I did not use Power Query. Would you mind explaining in more details?

As far as the region goes, I am not sure what the Form itself is set as, as it has a drop-down menu to select the Date of input.

 

[Alex Blakenburg]

Your screen shot showed "Get & Transform Data" from which I assumed you used Power Query.
What parts are under your control ?

• Google Sheets
  What is the Region / Locale setting here and can you change it, are the users in your region ?
  How to Change the Default Date Format in Google Sheets.

• The data is being imported from a Google Sheets document

  Imported from where to where and using what tools (is it Power Query)

• I copy the information imported from the Form to Google Sheets in my Excel file.

  Probably because I am not well acquainted with the Google docs environment I am unclear on the data flow here.
  I would have thought the From might populate a Google sheet and you somehow get the data from there.
  (probably overlaps 2 above)

 

[Sartosvrenht]

Google Sheets
What is the Region / Locale setting here and can you change it, are the users in your region ?
How to Change the Default Date Format in Google Sheets.

Thank you! This has resolved the Issue. The Locale on the File > Spreadsheet Settings was set to United States hence the reason why the MM/DD were being swapped! Appreciate it!

 

[Alex Blakenburg]

No problem. Glad we could help. Thanks for the feedback.