Formula Explanation

R

rition

Hello

I wanted to create a list with weekdays only and I have found this
formula which works perfectly but I wondered if someone could explain
it to me so that I can write one myself if I need to in the future.

=IF(WEEKDAY(A2+1)=7,A2+3,IF(WEEKDAY(A2+1)=1,A2+4,A2+1))

Thank you
 
P

Peo Sjoblom

The formula is not correct if you want the next Monday if the date in A2 is
either Fri, Sat or Sunday, it works for Fridays and Sundays but not for
Saturdays, if you put today's date in A2 it will return Wednesday the 15th

Change it to

=IF(WEEKDAY(A2+1)=7,A2+3,IF(WEEKDAY(A2+1)=1,A2+2,A2+1))

Weekday returns 1 for Sunday, 2 for Monday and so on up to 7 for Saturday,
so the IF function checks if the date is either a Friday or a Saturday, if
the former add 3 days, if the latter add 2 days, in all other cases add 1

Having said that you can copy date using the right mouse button and when you
release it you can select weekdays only

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
P

Pete_UK

This formula is incrementing the value in A2 - presumably the formula
is in A3.

The WEEKDAY( ) function acts on a date and returns a number which
represents the day of the week - 1 for Sunday, 2 for Monday etc up to 7
for Saturday. So, the first part of the formula

IF(WEEKDAY(A2+1)=7,A2+3

is saying that if 1 is added onto the date in A2 and this makes it a
Saturday then add 3 on instead to make it the following Monday. The
second part of the formula

,IF(WEEKDAY(A2+1)=1,A2+4

is saying that if the next day after the date in A2 is a Sunday, then
make this cell the following Wednesday. The third part of the formula

,A2+1))

is what happens if neither of the first two conditions are met, i.e. it
chooses the next day.

As long as you ensure that the date in A2 is not a Saturday or a
Sunday, then you can simplify the formula as follows:

=IF(WEEKDAY(A2+1)=7,A2+3,A2+1)

i.e. if the next day is a Saturday, then choose the following Monday
otherwise choose the next date in sequence.

Hope this helps.

Pete
 
D

Dana DeLouis

=IF(WEEKDAY(A2+1)=7,A2+3,IF(WEEKDAY(A2+1)=1,A2+2,A2+1))

Here's something a little different:
=A1+MOD(31744441,WEEKDAY(A1)+16)
 
D

daddylonglegs

A couple more suggestions.....

=A2+IF(WEEKDAY(A2)<6,1,9-WEEKDAY(A2))

or with WORKDAY function from Analysis ToolPak just

=WORKDAY(A2,1)


....but I love

=A1+MOD(31744441,WEEKDAY(A1)+16)

Dana,

Can you give me a couple of days to figure out how it works? If I can'
can I get back to you for an explanation
 
D

Dana DeLouis

Hi. Thanks. Sure...Let me know. However, I'm in the middle of a complete
re-write. For example, I'm trying to incorporate some of your ideas into my
program. If the data size were larger, we run into Excel's Mod bug. I'm
trying to insert a step where it spits out something like this prior to
another option:

=A1+MOD(51,MAX(5,WEEKDAY(A1)))

--
Dana DeLouis
Windows XP, Office 2003


"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
 
Top