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?

Improve this question
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 '20 at 17:54
  • @bkraines I use this method and not effected on rows other idea you have? – Farhood Naqizade Aug 21 '20 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 !! – Rajesh S Aug 23 '20 at 5:14
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.

Improve this answer
3
  • This is not the answer I was expect. – Farhood Naqizade Aug 22 '20 at 18:09
  • "keep an open mind" superuser.com/help/how-to-ask – FlexYourData Aug 22 '20 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 – Farhood Naqizade Aug 22 '20 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.