1

I export some files from a network as CSV and then I convert that file as excel file there is one column in excel file named-as (dates) now when I apply the filter on it the filter is not slowing correct info due to two different formats in dates. one of them is correct but the other one is not I want to make them same format but they are not customizing according to my need

sample code

in this image, the above two dates are according to my need and the rest which are aligned left is not in the format that I want

please help me to have them in the same format

Improve this question
4
  • Welcome to SuperUser! We are not a free coding service but a community of people interested in computers that are willing to share their knowledge and experience. If you have already tried something please post it here, otherwise this question is likely to be closed due to lack of preliminary work from your side.
    – DarkDiamond
    Commented Dec 14, 2022 at 8:05
  • 1
    How did you eport the .csv file to Excel? Did set the Date format for the column?
    – Lee
    Commented Dec 14, 2022 at 8:20
  • you should add a sample of the csv file, not just the result after importing; it looks as if the top cells use mm-dd-yy format, but the csv input is dd-mm-yy?
    – Paul
    Commented Dec 14, 2022 at 17:42
  • Do not OPEN the csv file. Rather you should IMPORT the file using either the legacy wizard or Power Query. If you do that, you will be able to tell Excel the format of the date column, before it misinterprets things.
    – Ron Rosenfeld
    Commented Dec 15, 2022 at 2:49

1 Answer 1

Reset to default
1

String vs. Number

Excel is interpreting some of your dates as text strings and some as numbers. The source data holds the root of the problem, and fixing that prior to export will eliminate your issue. If however you want to clean up the mix of formats after the fact, you can convert the text strings to proper dates using a formula.

Assumptions

These assumptions are for the dates stored as strings. Your actual dates will follow whatever format is applied to the cells.

  • the day, month and year are delimited with a slash /
  • the month and the day precede the year mm/dd/yy or dd/mm/yy
  • month and day can be one or two characters 2/8 or 02/8 or 2/08 etc.
  • the year can be 2 or 4 characters 12/21/2022 or 12/21/22
A B C D
1 Displayed Actual Value Converted Formatted
2 12/27/2020 44192 44192 12/27/20 0:00
3 12/20/2020 44185 44185 12/20/20 0:00
4 19/12/20 04:23:48 19/12/20 04:23:48 44184.18319 12/19/20 4:23
5 13/09/20 03:46:16 13/09/20 03:46:16 44087.15713 09/13/20 3:46
6 13/09/20 13/09/20 44087 09/13/20 0:00
7 13/9/20 05:51:10 13/9/20 05:51:10 44087.24387 09/13/20 5:51
8 13/9/2020 06:19:27 13/9/2020 06:19:27 44087.26351 09/13/20 6:19

Conversion Formula

Uses a minimal test to accept the displayed date value if it is a number, or else re-arrange its day and month. It doesn't know or care what the original and new order actually is, only that it will flip them.

In cell $C$2 in sample table above:

=BYROW(A2:A8, LAMBDA(x, IFERROR(x*1,
      TEXTJOIN("/",, INDEX(TEXTSPLIT(x,"/"),1,2),
                     INDEX(TEXTSPLIT(x,"/"),1,1),
                     INDEX(TEXTSPLIT(x,"/"),1,3))*1)))

Column C and column D in the sample table above have the same values. Column C is formatted as a decimal number and column D is formatted with an arbitrary Date/Time format.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .