I had a question during one of my Date and Times Webinars in February. It was about months and fortnights. I couldn’t answer it during the webinar, but I did follow up with an email with the solution. The answer follows.
The number of fortnights in a month can be either 2 or 3. The importance of knowing the number often relates to monthly cashflow forecasting. Fortnightly payrolls will cause two or three months of the year to have three fortnights.
There is probably an array formula that can work it out. I resorted to helper cells and a formula I could copy across. See image below. The file is available for download at the bottom of the post.
Cell B3 is an input cell for the end date of the fortnight involved. Cell B2 is intentionally empty.
The formula in cell C2 is
=IF(EOMONTH(C1,0)>=$B$3+((SUM($B$2:B2)+3)*14),3,2)
The secret is that the highest number of fortnights in a month is 3, all other months are 2. So all we have to do is identify those months where the end date of the third fortnight is less than or equal to the end of the month date.
The EOMONTH function returns the last calendar day of the month for a specific date.
We take the end date of the last fortnight prior to our time period in cell B3 and add 3 fortnights to it. Cell B2 is blank so SUM($B$2:B2) returns zero. We add 3 to zero and then multiply by 14 and add it to the end date to find out if the end of the third fortnight is less than or equal to the end of the month date. If it is, the IF function returns 3, otherwise it returns 2.
SUM($B$2:B2)
accumulates the previous month’s fortnights as the formula is copied across.
The whole formula needs cell B3; the dates in row 1 and the previous formulas in row 2 to calculate. The formula that can be copied across.
Cell C3 displays the last fortnight end date for each month. The formula is
=+B3+(C2*14)
Cell C3 and the cells to the right are not required for the calculation, but are there for information purposes only.
There may be an easier way to calculate this – feel free to share your thoughts in the comments section below. This technique works and doesn’t require too many helper cells, so I am happy with it.
Download the file at the link below.
Please note: I reserve the right to delete comments that are offensive or off-topic.