You said you are using the formula =U1-WEEKDAY(U1)+6
with the date in cell U1
being a Saturday. To answer your question, you need to work out what WEEKDAY(U1)
would return.
The Excel WEEKDAY()
function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday. Therefore, in your case WEEKDAY(U1)
would return a 7. This would turn your formula into the calculation of U1-7+6
.
One area that trips me up sometimes is that Excel does not follow the true BODMAS rule. It follows the formula from left to right unless there are brackets. It will calculate everything in brackets left to right first, and then calculate the rest left to right. If it followed BODMAS, it would return a result of U1-13
.
In Excel your formula would give you a result of U1-1
(the previous day).
If U1
was a Wednesday, it would be U1-4+6
which would give you 2 days after the day in U1
. Do you see what the formula is doing?
The formula is working out what date the Friday is in the Sunday-Saturday week for the date given in U1
.
If you want the Friday in the week following the date in U1
you could enclose the current formula in brackets and add 7 at the making the formula =(U1-WEEKDAY(U1)+6)+7
There would be a problem though with this formula. If you change your start date to a Wednesday, it will advance the first payday to the Friday in the following week thereby missing a payday.
If you wish to use your original formula, you will need to use an IF()
function to check if U1
is Friday or before first.
=IF(WEEKDAY(U1)=<6,U1-WEEKDAY(U1)+6,(U1-WEEKDAY(U1)+6)+7
A more efficient formula to use (short and without IF()
functions) is
=U1+(7-WEEKDAY(U1,2)+5)
The ,2
in the WEEKDAY()
function changes the weekday format from Sunday-Saturday to Monday-Sunday with Monday = 1