How to filter results by the day number?

Question

mihai179 Author

Level 1

9 points

How to filter results by the day number?

Hi, guys,

I need your help again.

I have this table in which I created two categories:

1. Date/Year

2. Date/Month.

I need to filter the results from G column by the day number in two ways:

1. I need to see the results for each 1st, 2nd, 3rd, ... , 31st of each month. Like each 1st, 2nd, 3rd, ... , 31st of each January, February, ... , December. So, if I have an 8 years range, I will have 8 results for January 1st, 8 results for January 2nd, ... , 8 results for December 31st;

2. And, I also need to see the results for each 1st, 2nd, 3rd, ... , 31st of any month. Like each 1st, 2nd, 3rd, ... , 31st for the entire period. In this case, the month doesn't matter. So, I if I have an 8 years range, I will have 8years*12months=96 results for each of 1st, 96 results for each of 2nd, ... , 96 results for each of 31st.

Can you, please, help?

Thank you!

Posted on Aug 29, 2021 5:53 PM

Reply

Answer 1

Badunit

Level 7

30,954 points

Aug 30, 2021 10:37 AM in response to mihai179

=DAY(A)

I've wanted to post a few times but I keep getting hung up on how to sort it all so that the month names and days are all in the right order when categorized. My fake data had missing days (for example, Jan 3, 2020 is missing but Jan 3, 2021 is present) and it would get the days out of order when I turned on Categories. Or I would try to sort the "month" group and it would do it alphabetically and I couldn't figure out how to get it back (other than Undo or turning off categories). Anyway, watch out to make sure your data is in the correct order.

Reply

Answer 2

Barry

Level 9

60,041 points

Aug 29, 2021 11:15 PM in response to mihai179

"So, I if I have an 8 years range, I will have 8years*12months=96 results for each of 1st, 96 results for each of 2nd, ... , 96 results for each of 31st."

Actually, you'll have 96 results for each of only the 1st to 28th day of all months, 94 results for the 29th day, 89 for five of the months, and 54 for the 31st of January, March, May, July, August, October and December.

"1. I need to see the results for each 1st, 2nd, 3rd, ... , 31st of each month. "

Add two columns to the table, one to contain the Month name, the other to contain the Day number. These must be text (month names) or numerical (Day numbers) values—NOT values that Numbers will interpret as parts of a Date and Time value.

For the need expressed above, Categorize the table using the month names, with a sub category using the day numbers.

"2. And, I also need to see the results for each 1st, 2nd, 3rd, ... , 31st of any month. Like each 1st, 2nd, 3rd, ... , 31st for the entire period."

Categorize the table on the Day numbers column, with no subcategories.

I'll leave the details to someone who has a 'post Jurassic' version of Numbers that supports categories. Mine's olderthan that. :-)

Regards,

Barry

Reply