Log In
Register
Home
October 18, 2018
Offline
This has me a bit baffled - there is a technique (a formula) for deriving the next day of the week following any other day of the week, eg. the next Friday following (the preceding) Wednesday. The formula is:
=A1+7 - WEEKDAY(A1+7 - 6), where A1 is the 'start date'.
The formula will generate the next following Friday (because the very last item in the formula before the closing bracket is the numeral 6 which represents, in Excel, Friday). So if I change that 6 to a 4 it will generate the next 'Wednesday'.
Within the WEEKDAY formula it advances the 'start date' by 7 days and then deducts Excel's 'day of the week' numeral, and then in turn evaluates that with Excel's 'day of the week' formula again - why does this work?
VIP
Trusted Members
December 7, 2016
Offline
Hello,
There is a good explanation here on how this formula works.
October 18, 2018
Offline
Yeah I've seen that one and it doesn't really do it for me ... the throw away line '... by subtracting the result of a calculation that uses the WEEKDAY function.' doesn't really help.
Oh well, I'll just have to die not knowing, as they say.
VIP
Trusted Members
December 7, 2016
Offline
Hello Kevin,
The linked explanation is in my view clear enough, it is all about mathemathics.
As you want to know the next occurence of a certain weekday from a certain date you need to add 7, if you want to know the second next occurence you add 14 etc. So this explains why you add with +7 in the formula.
What the WEEKDAY function gives you is the number of the day in the week, by default starting with Sunday as first day in the week and thus Saturday as the last, which then gives us following list (as you already know, just listing this for other readers).
Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7
So, what is happening in the WEEKDAY part of the formula is following:
WEEKDAY('Start Date' + 7 - 6) where Start Date = 17/7/2019.
Adding the numbers we then have following:
WEEKDAY(43663 + 7 - 6)
Adding the number 7 now.
WEEKDAY(43670 - 6) where 43670 = 24/7/2019
As we want to know next occurence of Friday, we subtract with 6, which corresponds to Friday in Excel's list of weekdays.
WEEKDAY(43664) which as a date is = 18/7/2019, which is a Thursday. So the result we get from this is 5.
So if we use the complete formula we will get following result:
='Start Date' + 7 - WEEKDAY('Start Date' + 7 - 6)
Speeding up a bit:
=43670 - 5
=43665, which is 19/7/2019, which is Friday this week.
Not sure if I managed to make it more clear, but it is just a matter of drilling down to the numbers.
1 Guest(s)
