Search results

I don't know why but this didn't work. Thanks anyways! Your solution worked. Thanks Buddy!

Hello Champs! Lately, I have been struggling to calculate No. of Days based on date criteria. I want to calculate No. of Days against each period in column C if the entered dates fall within the range based on dates entered in highlighted cells. Here is the screenshot of manual days...

Thanks @GraH - Guido for your solution, but unfortunately this didn't work either on my complete data set, it is giving wrong results and sometimes swapping date with months and vice versa. Have a look at the result when I applied it to around 20k record. The query has also converted non-dates...

Thanks Khalid for your response, but unfortunately this didn't work with complete data set may be because there are many different date formats used by person entering dates. Here is the screenshot of filter which shows incorrect years..

Hello everyone, I have come across somewhat similar problem as mentioned in this post with longer text length and different date formats which is manually entered, need null where there is no date. I tried to copy amazing solution provided by @Chihiro in my scenario but it didn't work perhaps...

Hello @Chihiro I have come across somewhat similar problem with longer text length and different date formats which is manually entered, need null where there is no date. I tried to copy your solution in my scenario but it didn't work perhaps due to my limited knowledge of Power Query. Could...

Hello, I have list of staff of various branches with following information, ID # Name Grade Designation Branch Code Following is the list of grades we have from top to bottom VP AVP OG-I OG-II OG-III Following is the list of designation we have in any of our branch Manager Assistant...

I unpivot columns through Power Query as you have suggested, but still unable to make Pivot Table that looks like the one I posted in my first post. Could you please look into the file and provide a solution. Thanks.

Thanks @Chihiro for responding. Unable to understand what you said, could you please a tutorial or web link explaining that in detail. Thanks.

Hello everyone, Please find attached a simple table with following columns. Region Branch Category BOM Status as of 30 June, 2021 BOM Status as of 31 July, 2021 BOM Status as of 31 Aug, 2021 I am trying to make a pivot table with Region wise summary of Category II and Above Branches which...

Hello everyone, I have a record of 8000+ plus employees of my department with the following headers. The data is updated at each month-end however, the headers remain the same, it's only the number of counts which usually changes. My boss is interested in knowing the variation in data on...

Apologies for asking in wrong section. I thought since this is a question, it should be posted in Ask an Excel Question section. Anyhow, is there any way I can move this post to Discuss Data Visualizations and Charting ?

@vletm Thanks for responding. Sample file can be downloaded from here. Sample File for Chandoo

Hello everyone, I have created this dashboard using power pivot with prime focus and calculation based on dates and grouping of years. The problem is order of axis changes with different slicer selection. I want to display axis in ascending order. I tried reordering/sorting the axis manually by...

Refer reply from @GraH - Guido above.

Try this, =INDEX(Data!$C$6:$F$42,MATCH(A2,Data!$B$6:$B$42,0),MATCH(B2,Data!$C$5:$F$5,0))*C2

ISBLANK will not work, since the cell isn't actually blank, it contains formula. The above formula is working for me, have a look. Are you looking for "Teen" whenever column D is blank? just like Row 5?

Try this, =IF(AND(D2>25,G2="F"),"Women",IF(AND(D2>25,G2="M"),"Men",IF(AND(D2>=20,D2<=25),"Young Adult",IF(OR(F2="Yes",D2<=19,LEN(D2)<1),"Teen"))))

Could you please upload sample file?

Removed +1 added in month. Try now =IF(AND(DAY(B8)>=11,DAY(B8)<=25),DATE(YEAR(B8),MONTH(B8),25),DATE(YEAR(B8),MONTH(B8),10))

Try this =IF(AND(DAY(B8)>=11,DAY(B8)<=25),DATE(YEAR(B8),MONTH(B8),25),DATE(YEAR(B8),MONTH(B8)+1,10))

I would have come up with this solution, =IF(A3="","",TEXT(A3,"ddd"))&IF(AND(A3<>"",C3="cool"),CONCATENATE("/",TEXT(A3+1,"ddd")),"") But thanks to @John Jairo V I have learned something new today, I wasn't able to figure out this part of his formula "\/ddd" but then I google it and got to know...

As stated above, through Find and Replace (Ctrl+H)

Enter this in new cell =TRANSPOSE(A3:A8) select A3:A8 and press F9. You will have what you want, copy and paste the array output. You may replace the special characters as desired through Find and Replace.

Not sure about about DAX formulas as I am not much into it, though CUBE function will work in conjunction with Power Pivot.