Apple numbers formula for date on the nth day of a given year

Has anyone a formula in numbers that will show the date when given the nth day of a given year?


eg


A formula that would return “3rd March” given 2018 (year) and 62 (number of days).

This formula should return “2nd March” if given 2024 (year) and 62 (number of days) since 2024 is a leap year.


Aware I could build a look up table, and work something out for multiples of four to account for leap years, but that feels like a clumsy solution.



Any ideas folks?



Posted on Sep 2, 2026 8:47 AM

Reply
Question marked as Top-ranking reply

Posted on Sep 2, 2026 9:16 AM

This is pretty easy. Numbers will happily add dates and days, so all you need is one cell (or calculation) that determines year, and another cell (or calculation) that has the day number.


For example:


Cell B2: year
Cell B3: days
Cell B4: =DATE(B2,1,1)+B3-1


The DATE() calculation works out the first day of the year given in cell B2. To this, you simply add the number of days from B3, and subtract 1 (since day 0 is the first of the year)


If you want the result to show the month name and no year you can just use cell formatting to display the date in whatever format you want.

If you specifically want '3rd March' (with the 'rd', 'th', 'nd', etc.) that's going to be more work...

2 replies
Question marked as Top-ranking reply

Sep 2, 2026 9:16 AM in response to Kestraz

This is pretty easy. Numbers will happily add dates and days, so all you need is one cell (or calculation) that determines year, and another cell (or calculation) that has the day number.


For example:


Cell B2: year
Cell B3: days
Cell B4: =DATE(B2,1,1)+B3-1


The DATE() calculation works out the first day of the year given in cell B2. To this, you simply add the number of days from B3, and subtract 1 (since day 0 is the first of the year)


If you want the result to show the month name and no year you can just use cell formatting to display the date in whatever format you want.

If you specifically want '3rd March' (with the 'rd', 'th', 'nd', etc.) that's going to be more work...

Apple numbers formula for date on the nth day of a given year

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.