Biweekly = every 2 weeks
I've seen many of the posts online about biweekly calculations. The part that is not helpful is that they are usually calculating between 2 known dates or using a reference date.
I'm trying to do the calculation without a specified reference date and just use the beginning of (excel) time - 1/1/1900, such that I can input a date and get the biweekly period # it is in and the biweekly start date without having to create a list.
Question A: Seeking Consistent Biweekly Formula
Using CEILING, I'm getting biweekly periods for a given month, however I am having to use different formulas. It would be helpful if it could be boiled down to a single formula.
For Feb 2021, the applicable start dates of the biweekly periods are:
- O13 = 2/1/21 - Start of Feb
- O16 = 2/14/21 - Formula result - calculated 2nd period in Feb
=CEILING((O13),14)-13 = 1/31/2021 -- 1st period that occurs partially within the month; ends within the month.=CEILING((O13+1),14)+1 = 2/14/2021 -- 2nd period w/in month [in cell O16, used below]=CEILING((O16+1),14)+1 = 2/28/2021 -- 3rd period starting within the month
These dates are turning out correct, though I would prefer a singular formula that could be filled down where that period before Feb starts is a different formula.
Question B: Biweekly Sequence Number Given Any One Date
Calculating the biweekly period number for the, 1 through 26 (sometimes 27 [roughly every 11 years]). I haven't found any good info on how to do this, given that the biweekly # resets on the first period each year. For instance, using the data above:
- 1/31/2021 = period #3
- 2/14/2021 = period #4
- 2/28/2021 = period #5
Tried:
=quotient(weeknum(biweekly start date)) which almost gets right results except on certain years, e.g 2012, 2023 - which happen to have 27 periods in the year, but 2000 has 27 as well and quotient gives the right answer.
Trying to figure this out in formulas first (logic and calculations), then will transfer it to VBA. So if there is an easier way in VBA, I'll take that too.
21 Answer
These single formula calculates Bi-weekly Dates:
:: Caveat ::
Since OP has not shared any Sample data or Screen shot therefore, I've shown methods calculates bi-weekly dates in two different situations.
Method 1:
Formula in cell Q4:
=IF(MONTH(N4+14)<>MONTH(N4),DATE(YEAR(N4),MONTH(N4)+1,1),N4+14)
Method 2:
Formula in cell S4:
=IF(AND(DAY(S3)=1,S3-S2<14),S2+14,MIN(S3+14,EOMONTH(S3,0)+1))
N.B.
- Keep cell S2 blank & enter the start Date in cell S3.
- You may use any of them, as per your requirements.
- Adjust cell references in the formula as needed.
