You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I have a numbers spreadsheet of my Great Grand kids with their birthdates over many years. How do I sort the table by "day of the year"? I can of course sort by their birthdays including the year but I just want to sort them by the calendar day
Assuming your dates are in column B,
Insert a new column (I'll use column C for this example)
Formula in column C =MONTH(B)+DAY(B)/100
Sort using column C
Don't put this formula in any empty rows or it will sort them to the top. You may want to remove any empty rows at the bottom of the table, if it has any.
Assuming your dates are in column B,
Insert a new column (I'll use column C for this example)
Formula in column C =MONTH(B)+DAY(B)/100
Sort using column C
Don't put this formula in any empty rows or it will sort them to the top. You may want to remove any empty rows at the bottom of the table, if it has any.
HI Ken,
As Badunit has already said, and you have found with a closer look, Numbers replaced the division operator ( / ) with the division sign ( ÷ ).
Did you also notice in the formula that you show below, Numbers has replaced the multiplication operator ( * ) with the multiplication sign ( x )?
That x is not the letter x, as can be seen in a side by side comparison (I've used a screen shot and larger size as the differences are pretty subtle in Helvetica):
Two letters with the multiplication sign between.
Apple made this change a couple of years ago, And there was great confusion regarding the change to ÷, but not a whisper in these forums from people noticing the change from * to the multiplication sign. Eventually, posts regarding / and ÷ stopped coming in daily, weekly, or even monthly.
I was surprised this week to see two posts on the subject—yours, where it wasn't the main topic, and another, four days ago, where it was.
If instead I use C=MONTH(B) and D=DAY(B) and E=(C)x100+(D), I get e.g., 101 for January 1 and 601 for June 1. Notice here that I am using x instead of /, but the results give me a number that I can sort.
Nicely done. Works just as well as the similar formula suggested above.
Regards,
Barry
Very nice solution but for some reason I was unable to use "/" as a divisor. I did though use the same approach by using a separate columns for MON and DAY and then combining them numerically in a third column. The results are what matter and it gives me what I was looking for.
A a couple of questions and a comment:
1) Do you have any idea as why I am unable use math operators other than "+" in Numbers? ((Ver 12.1) (running on an M1)).
2) I am familiar with Excel, having used it for many years, and extracting calendar days is much simpler.
3) Could the operation of extracting the calendar day for the year be done using a Pivot table?
What happens when you try to use the operators other than + ? Without them you won't be able to create formulas.
How do you extract the calendar day in Excel that is easier?
I don't know much about pivot tables. If you can do it in Excel then maybe also in Numbers
Apparently Formulas involving most everything except dates work as expected. When I use your formula: C=MONTH(B)+DAY(B)/100, the only thing I get is C=MONTH(B) + DAY(B)+100. Numbers will not allow me to type a "/" and instead inserts the "+" operator.
If instead I use C=MONTH(B) and D=DAY(B) and E=(C)x100+(D), I get e.g., 101 for January 1 and 601 for June 1. Notice here that I am using x instead of /, but the results give me a number that I can sort.
As regards Excel: If my memory serves me well I used the Function TEXT(B)("mmdd") (or something along that line) but I understand that Excel 365 has a new SORTBY function designed expressly for sorting by Birthdays, Anniversaries, etc.
Look more closely at the + that should be a /. It is actually a ÷ division symbol. There are more than a few other posts in this forum about this substitution of / to ÷ . I absolutely hate it. The formula result should have been correct regardless.
Hi Again,
Ken wrote: Apparently Formulas involving most everything except dates work as expected.
Assuming "as expected" is flavoured with "as in Excel", there's much truth in that.
If you used many 'array' formulas (identifiable by their pairs of {curly brackets}, you'll likely find at least some of them are also not supported in Numbers.
Date and Time formulas are different because of the way the two applications store these values. In Excel, a date is stored as a serial number, which stands for the number of days that have passed since 'day 1'
In Numbers, Date and Time is a single value including both the date and the time of day. If you enter only the Date part, Numbers sets the Time of Day part to 0:00:00.000 or 'midnight, at the beginning of that day.
If you enter only the Time of Day part, Numbers automatically attaches the date on which the entry was made.
If you enter both parts, Numbers stores the full Date and Time value that you entered.
"Amount of 'time' that has passed" is a Duration value, which is different from a Date and Time value.
It's an interesting journey. :^)
Regards,
Barry
When I use your formula: C=MONTH(B)+DAY(B)/100, the only thing I get is C=MONTH(B) + DAY(B)+100. Numbers will not allow me to type a "/" and instead inserts the "+" operator.
Not only an interesting journey but a frustrating one as well. When one only uses a program once in a great while one expects that most things remain the same and only new features might be added. But that is not the case, is it. I have see this all too often over the years and now come to expect basic changes but regret the wasted time in having to re-learn or re-train my mind or fingers. As to this last, I hope that because most people use a telephone keypad while only only a few of us use a numeric keypad that someday Apple might decide to change it in an attempt to unify it.
You are absolutely correct in that the operative, which to me looked like plus sign is actually a division sign.
Thanks for pointing that out. I appreciate it
Ken
Sort table by calendar day from the Date
