Find the middle day of the year

Yesterday (2nd July) was the exact middle point of 2022, so today (3rd July) is the first day of the year’s second half. 😯😬

It’s common knowledge that there are 365 days in a year (except leap years). As 365 ÷ 2 = 182.5, the 183rd day (2nd July) is sandwiched between two blocks of 182 days.

To calculate this in Excel, you must find the median of the dates. This is the middle value when a set of numbers are placed in ascending order.

Remember, dates in Excel are stored as serial numbers that count the elapsed days starting from 1st January 1900.

The dates below this point are in the format DD/MM/YYYY.

For example, the serial number of 01/01/1900 is 1, 02/01/1900 is 2, and 03/07/2022 is 44744.

On page 2, the median date of 2022 has been calculated and is shown in reader-friendly and serial form.

Let’s look at the formula:

=MEDIAN(
SEQUENCE(
DAYS("01/01/2023","01/01/2022"),,
DATE(2022,1,1)
)
)

SEQUENCE is used to generate an array of date values. In the 𝚛𝚘𝚠𝚜 argument, DAYS returns the number of days between (and including) 01/01/2022 and 31/12/2022. It’s necessary to specify a 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎 that is one greater than the final one to include, as it isn’t counted.

At this point, we have an array of sequential numbers from 1 to 365. That is only giving us the serial dates for 1900 though, so we put DATE(2022,1,1) in [start] to return the serial date for 1st January 2022, which is 44562. The array now goes from 44562 (01/01/2022) to 44926 (31/12/2022).

Nesting the SEQUENCE statement inside MEDIAN extracts the middle serial number from the array, which is 44744. Apply the date format and you get 02/07/2022.