Categorise a date based upon a conditional date range

Question

Categorise a date based upon a conditional date range

Office worker 125

Hi,

I have a column of different dates (DD/MM/YYY) and I want to create a formula to look up each date and, based upon a specified date range, report that date as a pre-specified category in a different column.

In the example below, I want the formula to look up each cell in the date column and if that date falls within the range 01/11/2025 to 30/11/2025 then it will return a value of "Spring" in the Category column. Likewise 01/12/2025 to 28/02/2026 will return "Summer", 01/03/2026 to 31/05/2026 will return "Autumn" and so on. How can I do this?

User's image

1 answer

Your answer

Answer 1

Dear @Office worker,

Thank you for posting your question in the Microsoft Q&A forum.

Based on your description, you want to create an Excel formula that categorizes each date into a season (e.g., Spring, Summer, Autumn, Winter) based on specified date ranges.

Here are some suggestions you can try:

You could achieve this in Excel using an IF formula combined with AND conditions to check whether the date falls within a specific range.

=IF(AND(A2>=DATE(2025,11,1),A2<=DATE(2025,11,30)),"Spring",
IF(AND(A2>=DATE(2025,12,1),A2<=DATE(2026,2,28)),"Summer",
IF(AND(A2>=DATE(2026,3,1),A2<=DATE(2026,5,31)),"Autumn",
IF(AND(A2>=DATE(2026,6,1),A2<=DATE(2026,8,31)),"Winter

Explanation:

DATE(year,month,day) ensures Excel interprets the date correctly.

AND(condition1, condition2) checks if the date is within the start and end range.

Nested IF statements return the correct category.

I hope information above meet your expectation, if I misunderstand your point, please pardon me and provide me more detail information so we could support you more effectively. If you have any other questions, please feel free to reach out.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Darren-Ng 5,640 Reputation points Microsoft External Staff Moderator

2025-12-05T06:00:15.6833333+00:00

Dear @Office worker,

Just checking in to see if the above information was helpful. If you have any further updates on this issue, please feel free to post back. If you think my reply is helpful to you, please remember to mark it as an answer. Warm thanks.
Barry Schwarz 4,786 Reputation points

2025-12-05T16:14:53.9+00:00

Using one IFS function will be much simpler than a bunch of nested IF statements, especially when you need to balance the closing parentheses.

=IFS(AND(A2<=...,A2>=...),"Spring, AND(...),"Summer", ...,"Fall", ...,"Winter")

Share via

Categorise a date based upon a conditional date range

1 answer

Your answer