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.
๐๐ฉ๐ฆ ๐ฅ๐ข๐ต๐ฆ๐ด ๐ฃ๐ฆ๐ญ๐ฐ๐ธ ๐ต๐ฉ๐ช๐ด ๐ฑ๐ฐ๐ช๐ฏ๐ต ๐ข๐ณ๐ฆ ๐ช๐ฏ ๐ต๐ฉ๐ฆ ๐ง๐ฐ๐ณ๐ฎ๐ข๐ต ๐๐/๐๐/๐ ๐ ๐ ๐ .
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:
=๐ผ๐ด๐ณ๐ธ๐ฐ๐ฝ(
๐๐ด๐๐๐ด๐ฝ๐ฒ๐ด(
๐ณ๐ฐ๐๐(“0๐ท/0๐ท/๐ธ0๐ธ๐น”,”0๐ท/0๐ท/๐ธ0๐ธ๐ธ”),,
๐ณ๐ฐ๐๐ด(๐ธ0๐ธ๐ธ,๐ท,๐ท)
)
)
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 ๐ณ๐ฐ๐๐ด(๐ธ0๐ธ๐ธ,๐ท,๐ท) in [๐๐๐๐๐] 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.
