0

Hello I have Table with 8K rows and This table from xls file import to excel and When import the file I pick the column to YDM (This column Have date with persian Jalali Format like : 1399/05/29) and after import file I'm Using Format date to yyyy/mm/dd but can't use pivteTable Group date in this Column After sometime i figure out the problem is the excel didn't understand the cell have date value! after click on cells and Editable the cell and hit enter the format accepted and now my problem solve! but for 8k Rows I can't do that , So any Comment or suggestion have for me?

3
  • Sounds like you have a column of dates that are stored as text which you need to convert to numbers. Generally, this can be done with the =DATEVALUE() worksheet function, but that will fail on Jalali dates. There must be a way specify the date format that Excel should recognize, in this case [$-fa-IR,16]dd/mm/yyyy;@. I bet it's doable in PowerQuery.
    – bkraines
    Aug 21, 2020 at 17:54
  • @bkraines I use this method and not effected on rows other idea you have? Aug 21, 2020 at 18:35
  • @FarhoodNaqizade,, you mean to say after applying new date format,,, when you try PT and group on date, it doesn't works ?? If YES,,, then U need HELPER COLUMN,,, get either Year or Month or Even Date as Helper value and use that to GROUP data,, check formula below,,, #FlexYourData has shown !! Aug 23, 2020 at 5:14

1 Answer 1

0

For this I recommend just creating the columns with formulas. For example:

enter image description here

Year:

=INT(LEFT([@YDM],4))

Month:

=INT(MID([@YDM],6,2))

Day:

=INT(RIGHT([@YDM],2))

Then you can use those columns in the pivot table.

3
  • This is not the answer I was expect. Aug 22, 2020 at 18:09
  • "keep an open mind" superuser.com/help/how-to-ask Aug 22, 2020 at 18:25
  • Your answer can use in answer or something like that but We need create new column . I have some data export every day and I need some straighten answer to solve the problem Aug 22, 2020 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.