1

I am making a spreadsheet for my personal financial needs and my table initiates with June 1, a Saturday.

I am using the formula =U1-WEEKDAY(U1)+6 to calculate each Friday for payday.

In my table, the formula is entered where the 5/31 is displaying. For the subsequent dates, I am taking the cell above +7. I am unsure as to why the same formula I've used elsewhere would cause the first record to be 5/31 in just this table, the day BEFORE the initial seed date. 5/31 is already factored in for the month of May.

I would appreciate any insight into what I did wrong so I can fix it.

The formula for the other 11 months of the year.

Problem Table:
Problem Table

Improve this question
1
  • You formula always results in the previous day for any given Saturday. Could it be that June is the only month you have (so far) where the first day is a Saturday? Doing a search for "Excel first Friday in month" gives multiple results on ways to get the first Friday in any given month
    – cybernetic.nomad
    Commented Oct 30, 2018 at 22:22

1 Answer 1

Reset to default
0

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

Improve this answer
2
  • If I use "=IF(WEEKDAY(U1)=<6,U1-WEEKDAY(U1)+6,(U1-WEEKDAY(U1)+6)+7" in other places in the spreadsheet to prevent this "error" from happening again, would this formula break the rest of my spreadsheet? This table is one of twelve for the entire year and this can't be the only time this ever happens.
    – Crystal Collins
    Commented Oct 31, 2018 at 14:27
  • @CrystalCollins - If the spreadsheet layout is the same then you can use it in all the sheets
    – Chris Rogers
    Commented Oct 31, 2018 at 14:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .