May 30 2023 01:42 AM
May 30 2023 01:42 AM
I've a table with below details:
Column A: Activity Name
Column B: Start Date
Column C: End Date
Now an activity can be completed in a day, so the start and end date are the same or it can even span over few months. Is there a way I can create a filter where it would list all the months where there is/are activity(s) occurring (in MMMM YYYY format) and when I select a month from that filter it would return the activities that are occurring that month. For example, if Activity1 has a start date 13/05/2023 and end date 15/07/2023 then there would be May 2023, June 2023 and July 2023 in the filter and even if I click June the activity would appear as the month June 2023 falls between the start and end date of that activity.
Please note, I can't use any macro and probably only limited to excel (office 365) with power query.
Any idea would be highly appreciated as I'm learning excel. Thank you :)
May 31 2023 06:02 AM
May 31 2023 07:14 AM - edited May 31 2023 07:29 AM
There's probably something to do. However, what "MMMM yyyy" - or any other value i.e. null - should be returned in such a case?
May 31 2023 11:09 PM
@L z. thanks for helping me out. I've attached a mockup spreadsheet so that I probably be able to explain my problem better. In the spreadsheet, the Sheet1 contains the raw data with sheet 'Table1' is the output of query. Now you would notice that there may be some blank rows in Table1, especially some may have start date but not the end date, some may not have both and others may be completely blank rows. I want these incomplete rows not to be included in the output of the query.
Therefore, incorporating your previous solution to get the period for each activity I want to create a pivot slicer with list of months that allows me to filter the activities that are occurring that month.
Again, thank you so much for your help with this. :)
Jun 01 2023 12:46 AM
there may be some blank rows in Table1, especially some may have start date but not the end date, so
what did you mean blank start end date in Table1?
They are all filled in the attachment.
And Table1 and Sheet1 are same?
pivot slicer with list of months that allows me to filter the activities that are occurring that month.
Jun 01 2023 12:51 AM
excel on windows. But the spreadsheet actually saved in sharepoint for collaboration. So other user can use it from other platform. Does it matter?
I was thinking of using the Data Model (Power Pivot). Will avoid then...
Jun 01 2023 01:27 AM
@peiyezhu sorry my bad. say there are rows with incomplete entry, e.g. start date there but not the end date, vice versa or both the columns are empty or even the complete row is empty (data yet to be entered).
blank means no value entered (null)
yes, that’s the ultimate goal but in order to get there I would need a query that would list all months where an activity occuring between the start and end date.
does that help?
Jun 03 2023 03:42 AM
@L z. Thanks for your help. Its really working. However, I've encountered another problem while solving one. For example, if I create a pivot table where I want to see the count of activities based on progress status it counts one activity 5 times where that activity spans over 5 months. In the below example, although there are only 4 distinct activities, Activity1 was counted 6 times since the start date was 23/03/2023 and end date was 25/04/2023 and 3 Person was involved, hence 3x2=6.
Could you please advise how can I resolve this so that if I create such pivot if would only reflect the correct number which is 4 with 1 completed, 2 in progress and 1 is planned. I've attached the spreadsheet for your convenience. Thanks once again.