Hello, I have a column, "Date Completed" with various dates that tasks have been completed. My company has a set calendar month that differs from the standard calendar. Is there a way, preferably with a slicer, to filter my "Date Completed" column based on the corporate calendar? For example, if I select January it returns all dates that fall within the corporate January dates. Thank you for any assistance!
It would probably be easier if we had a sample file to work with. Please read the yellow banner at the top of the page and attach a good sample file.
Without any sample data... I suggest a helper column to translate your 'Date Completed' into the correct 'Company month'.
This helper column should be formatted as YYYY MM =Text(d2,"yyyy - mm").
The reason for this is so that the slicer will show the dates in a logical order by year then month rather than alphanumeric.
The year is not required if your data is always in just one year.
If you need more help, please upload a sample file with a manual working of what the result should be so we know how the logic of your standard calendar vs. company calendar works.
If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.
I apologize. I should have provided a sample. Attached is the worksheet. I have a date slicer currently and would like to still use that functionality but in a way that utilizes the corporate calendar dates. Thank you again for your time and consideration.
Under your "Month" Column, you could enter this:
=INDEX($M$2:$X$2,,MIN(IF($M$3:$X$37=[@[Start Date]],COLUMN($A:$L))))
See attached.
Good day. Thank you, this works great. I have run into one snag though. Some of my reports have a Date/Time Opened column formatted as Date/Time. This is from the source and I can't format it otherwise. I tried formatting my corporate calendar to Date/Time but I keep getting the #N/A error. I tried formatting the Date/Time Opened column to Date but it still sees it as the source formatting and I get the same error. Any ideas?
Wrap the Date time in the INT (Integer) formula. Also, your formula had the COLUMNS going from A to O. It needs to be 12 columns, A to L
To create a date filter based on a corporate calendar in Excel:
Create a new column: First, create a new column that maps your standard calendar months to corporate calendar months. You might need to create a lookup table for this.
Use a slicer: Insert a slicer based on the corporate calendar months. Link this slicer to your "Date Completed" column.
Filter using the slicer: Now, when you select a corporate calendar month in the slicer, it will filter your "Date Completed" column to show only the dates falling within that corporate month.
This approach lets you filter your data based on your corporate calendar while keeping the original date values intact.
That worked great. Thank you.
Awesome. Thank you for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks