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

formatting date

mohan08

Member
Hi All,


Requesting help convert the Column B date format (Wrong conversion of date) to column F.

Tried changing date format highlight in yellow (Column J to M) using formulas, but still getting wrong conversion required your suggestion for date conversion
 

Attachments

  • Excel2.xlsx
    13.2 KB · Views: 9
Try this copied down:

=--B2

Format the column as short date.

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
1
MonthDayDateMonthActual DateDateMonthAliGW
2
05​
05/07/2019​
05July
07/05/2019​
07May
05/07/2019​
3
05​
15/05/201915May
15/05/2019​
15May
15/05/2019​
4
05​
17/05/201917May
17/05/2019​
17May
17/05/2019​
5
05​
22/05/201922May
22/05/2019​
22May
22/05/2019​
6
05​
31/05/201931May
31/05/2019​
31May
31/05/2019​
7
06​
25/06/201925Jun
25/06/2019​
25June
25/06/2019​
8
06​
27/06/201927Jun
27/06/2019​
27June
27/06/2019​
9
06​
06/03/2019​
06Mar
03/06/2019​
03June
06/03/2019​
10
06​
13/06/201913Jun
13/06/2019​
13June
13/06/2019​
11
06​
26/06/201926Jun
26/06/2019​
26June
26/06/2019​
12
07​
07/01/2019​
07Jan
01/07/2019​
01July
07/01/2019​
13
07​
07/08/2019​
07Aug
08/07/2019​
08July
07/08/2019​
14
07​
19/07/201919Jul
19/07/2019​
19July
19/07/2019​
15
07​
22/07/201922Jul
22/07/2019​
22July
22/07/2019​
Sheet: Text
 
mohan08
Which date layout do You normally use ( mm/dd/yyyy or dd/mm/yyyy) ?
Have You copy & pasted (some) of those datas from ... somewhere?
Dates could make mess ...
Fix:
1) You should know correct date
2) Copy from any 'correct looking date' to 'wrong looking date' and edit needed correct date
 

Attachments

  • Excel2.xlsx
    13.6 KB · Views: 2
I use the MM/DD/YYYY format, all the dates is copied for Power bi table, tried changing the date format but not able to have same format for all the dates in B column
 
mohan08
As You're using mm/dd/yyyy and You would like to see dd/mm/yyyy - there could be challenges to convert...
If Your Actual Dates are from May to July then even those green dates could do 'my way'.
Screenshot 2019-08-06 at 15.19.02.png
It would be useful to use format dd-mmm-yyyy ... then many times both date-formatted dates would 'act' as wanted.

AliGW ... for me, Your =--B2 didn't work ( and I didn't notice any changes in Your sent snapshot ).
 
Hi !​
mohan08, what are your Windows regional settings for dates ?​
How do you import the data ? It's weird as Excel as all the necessary to well import dates whatever the format used …​
 
Power BI knows "local" format, so it might well be different from "source" format. You need to know the source format in order to correct "local" format via the settings in the conversion dialogue box.

When you copy from PowerBI at least align the result of BI table to be compatible with your Excel Local settings first.
Then the issue is gone.

Tried this based on the above assumption on the format:
[O2] "WrongDateAsText"= IF(ISNUMBER(B2),TEXT(B2;"mm/dd/")&YEAR(B2),B2)
[P2] "CorrectDate" =DATE(RIGHT(O2,4),MID(O2,4,2),LEFT(O2,2))
 

Attachments

  • Copy of Excel2-3.xlsx
    14.1 KB · Views: 4
Glad it works out for you, still it sounds better to have to format correct in PowerBI, or replace the copy/paste by an import if possible. That enables you to control the format.
 
Back
Top