workday AND weekday

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

=WORKDAY(C22+$B23-1,1,HOLIDAYS

I have the above formula (compliments of you guys!) and would like to know if there is any way to force this to display as a SATURDAY.
For example, cell C22=4/27/04 and cell B23=1, my answer should be 4/28/04 (assuming no holidays fall within the date parameters...see how much I've learned!) but I would LIKE the answer to be 5/1/04. Can you help me do this

Gratefully....
 
=WORKDAY(C22+$B23-1,1,HOLIDAYS)
Click to expand...

=text(WORKDAY(C22+$B23-1,1,HOLIDAYS),"dddd")
will display the day, assuming your holidays list is correct, or omitted.
From Help:

Holidays is an optional list of one or more dates to exclude from the
working calendar, such as state and federal holidays and floating holidays.
The list can be either a range of cells that contain the dates or an array
constant of the serial numbers that represent the dates.



--
Don Guillett
SalesAid Software
(e-mail address removed)
marcy said:
=WORKDAY(C22+$B23-1,1,HOLIDAYS)

I have the above formula (compliments of you guys!) and would like to know
Click to expand...
if there is any way to force this to display as a SATURDAY.
For example, cell C22=4/27/04 and cell B23=1, my answer should be 4/28/04
Click to expand...
(assuming no holidays fall within the date parameters...see how much I've
learned!) but I would LIKE the answer to be 5/1/04. Can you help me do this?
 
Permit me one more question, please. Can you identify the qualifier for Saturday if I should want to change from a Saturday to a Wednesday, for example? Would I change the 7 to a 4?

Thank you.
 
Try this:

=WORKDAY($B23+C22-1,1,HOLIDAYS)+7-MOD(WORKDAY($B23+C22-1,1,HOLIDAYS),7)

--
Charles
www.officezealot.com


marcy said:
=WORKDAY(C22+$B23-1,1,HOLIDAYS)

I have the above formula (compliments of you guys!) and would like to know
Click to expand...
if there is any way to force this to display as a SATURDAY.
For example, cell C22=4/27/04 and cell B23=1, my answer should be 4/28/04
Click to expand...
(assuming no holidays fall within the date parameters...see how much I've
learned!) but I would LIKE the answer to be 5/1/04. Can you help me do this?
 
Marcy,

Try

=WORKDAY(C22+$B23-1,1,HOLIDAYS)+(7-WEEKDAY(WORKDAY(C22+$B23-1,1,HOLIDAYS),1)
)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

marcy said:
=WORKDAY(C22+$B23-1,1,HOLIDAYS)

I have the above formula (compliments of you guys!) and would like to know
Click to expand...
if there is any way to force this to display as a SATURDAY.
For example, cell C22=4/27/04 and cell B23=1, my answer should be 4/28/04
Click to expand...
(assuming no holidays fall within the date parameters...see how much I've
learned!) but I would LIKE the answer to be 5/1/04. Can you help me do this?
 
Thanks gentlemen. But, please tell me what is the identifier for Saturday so that should I need to change the argument from Saturday to Wednesday, where (what character in the formula) it would be changed? And, would it be from a 7 to a 4, for Wednesday

Thanks...
 
Ok, but what identifies the Saturday in the formula? What if I want to change the SAturday to a Tuesday for example?
 
Marcy,

You nailed it! It is the the 'magicial' use of 7 - the MOD formual that
determines the day of the week here.

You can determine the day of the week with Excel from the result of the
formula:

=MOD(TODAY(),7)

Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 0


Then by adding and subtracting to suit your needs, determine a new date.


________________


So in your specific case, its the FIRST DIGIT (SEVEN in this case), not the
second, that you would change to get a different date:

=WORKDAY($B23+C22-1,1,HOLIDAYS)+7-MOD(WORKDAY($B23+C22-1,1,HOLIDAYS),7)

Becomes 4 if you want Wednesday:

=WORKDAY($B23+C22-1,1,HOLIDAYS)+4-MOD(WORKDAY($B23+C22-1,1,HOLIDAYS),7)

Becomes 1 if you want Sunday:

=WORKDAY($B23+C22-1,1,HOLIDAYS)+1-MOD(WORKDAY($B23+C22-1,1,HOLIDAYS),7)

Becomes 0 if you want LAST Saturday:

=WORKDAY($B23+C22-1,1,HOLIDAYS)+0-MOD(WORKDAY($B23+C22-1,1,HOLIDAYS),7)


--
Charles
www.officezealot.com


marcy said:
Thanks gentlemen. But, please tell me what is the identifier for Saturday
Click to expand...
so that should I need to change the argument from Saturday to Wednesday,
where (what character in the formula) it would be changed? And, would it be
from a 7 to a 4, for Wednesday?
 
Ok...here's what happened when I used the formula and tried to change the argument to a Tuesday

E23=5/13/0
B24=1 da
E24=has formula: WORKDAY(E23+$B24-1,1,HOLIDAYS)+(3-WEEKDAY(WORKDAY(E23+$B24-1,1,HOLIDAYS)))
where I tried to substitute a 3 (for Tuesday) vs. the 7 in the original formula (for Saturday) but the result was 5/11/04. This took me back to the previous Tuesday and didn't add the 1 day event to the 5/13/04. The answer I was hoping for is 5/18/04

What am I missing? Please don't give up on me yet, guys...
 
=WORKDAY(C22+$B23-1,1,HOLIDAYS)

I have the above formula (compliments of you guys!) and would like to know if there is any way to force this to display as a SATURDAY.
For example, cell C22=4/27/04 and cell B23=1, my answer should be 4/28/04 (assuming no holidays fall within the date parameters...see how much I've learned!) but I would LIKE the answer to be 5/1/04. Can you help me do this?

Gratefully....
Click to expand...

Your question is not clear.

Do you really want the result to just DISPLAY as a Saturday, or do you want the
result to actually BE a Saturday?

Do you ALWAYS want it to be the subsequent Saturday, or do you want the closest
Saturday?

Do you ever want it to BE or DISPLAY as some other day of the week?






--ron
 
Marcy,
The problem you are running into is that Saturday is day seven of the week. It makes my original formula work because you will never get a negative number when you subtract the weekday from 7.

In order to use a different day the formula gets a bit more complex.

=WORKDAY(C22+$B23-1,1,HOLIDAYS)+(3-WEEKDAY(WORKDAY(C22+$B23-1,1,HOLIDAYS)))+IF(WEEKDAY(WORKDAY(C22+$B23-1,1,HOLIDAYS))>3,7,0)

The first part calculates the date, the second parts changes it to the Tuesday of the same week(because of the 3), and the third part adds one week if the original calculated date was after tuesday(also because of the 3).

A simpler formula would be:

=WORKDAY(E23+$B24-1,1,HOLIDAYS)-WEEKDAY(WORKDAY(E23+$B24-1,1,HOLIDAYS)-3)+7

but it is a lot harder to explain. Suffice it to say that the -3 would be changed to the number for the day you prefer

1-S
2-M
3-T
4-W
5-T
6-F
7-S

However, neither of these formula will take into account if the final day is a holiday.

I hope you aren't even more confused. I know I am.

Good Luck,
Mark Graesser
(e-mail address removed)

----- marcy wrote: -----

Ok...here's what happened when I used the formula and tried to change the argument to a Tuesday:

E23=5/13/04
B24=1 day
E24=has formula: WORKDAY(E23+$B24-1,1,HOLIDAYS)+(3-WEEKDAY(WORKDAY(E23+$B24-1,1,HOLIDAYS)))
where I tried to substitute a 3 (for Tuesday) vs. the 7 in the original formula (for Saturday) but the result was 5/11/04. This took me back to the previous Tuesday and didn't add the 1 day event to the 5/13/04. The answer I was hoping for is 5/18/04.

What am I missing? Please don't give up on me yet, guys...
 
Thank you, Charles
But, here's what happened:
cell G23 = 5/5/0
Cell b24 = 1 (for a 1 day event
Cell G24 = using your formula (and changing it to a 3 for a tuesday):
=WORKDAY(G23+$B24-1,1,HOLIDAYS)+3-MOD(WORKDAY(G23+$B24-1,1,HOLIDAYS),7
The answer came up as 5/4/04, but it should have been 5/11/04. That is, 5/5/04 + 1 = 5/6/04 and loop to the next Tuesday
So, what do I need to do to correct
Thank you for your patience!!
 
Marcy,

OK try this

=WORKDAY(C22+$B23-1,1,HOLIDAYS)+CHOOSE(WEEKDAY(WORKDAY(C22+$B23-1,1,HOLIDAYS
),1),6,5,4,3,2,1,0)

This gets the next Sat. To get the next Mon, change the sequence
6,5,4,3,2,1,0 to
1,0,6,5,4,3,2

For Tue, change it to
2,1,0,6,5,4,3

Wed to
3,2,1,0,6,5,4
etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

marcy said:
Ok...here's what happened when I used the formula and tried to change the argument to a Tuesday:

E23=5/13/04
B24=1 day
E24=has formula: WORKDAY(E23+$B24-1,1,HOLIDAYS)+(3-WEEKDAY(WORKDAY(E23+$B24-1,1,HOLIDAYS)))
where I tried to substitute a 3 (for Tuesday) vs. the 7 in the original
Click to expand...
formula (for Saturday) but the result was 5/11/04. This took me back to the
previous Tuesday and didn't add the 1 day event to the 5/13/04. The answer
I was hoping for is 5/18/04.
 
Thanks, Mark. You are a life-saver.

I think, at this point, if the resultant date happens to land on a holiday, I will overwrite it MANUALLY. (or merely pretend I didn't notice when my boss will, no doubt, point it out!!

Thank you so much for your kind attention
 
Yes, Ron, I do want the answer to be either a subsequent Saturday or the Saturday if it happens to fall on one but NEVER the PREVIOUS SAturday as I cannot go backwards for the events

And, yes...I will need to change the day from Saturday to Tuesday or Thursday as the date of departures for vessels varies from country to country

Thanks so much for your continued help (and patience!)
 
It certainly did help...Thanks to everyone for helping me!!
You will be making me look so good at work tomorrow!!
----- Bob Phillips wrote: -----

Marcy,

OK try this

=WORKDAY(C22+$B23-1,1,HOLIDAYS)+CHOOSE(WEEKDAY(WORKDAY(C22+$B23-1,1,HOLIDAYS
),1),6,5,4,3,2,1,0)

This gets the next Sat. To get the next Mon, change the sequence
6,5,4,3,2,1,0 to
1,0,6,5,4,3,2

For Tue, change it to
2,1,0,6,5,4,3

Wed to
3,2,1,0,6,5,4
etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

marcy said:
Ok...here's what happened when I used the formula and tried to change the argument to a Tuesday:
B24=1 day
E24=has formula: WORKDAY(E23+$B24-1,1,HOLIDAYS)+(3-WEEKDAY(WORKDAY(E23+$B24-1,1,HOLIDAYS)))
where I tried to substitute a 3 (for Tuesday) vs. the 7 in the original
Click to expand...
formula (for Saturday) but the result was 5/11/04. This took me back to the
previous Tuesday and didn't add the 1 day event to the 5/13/04. The answer
I was hoping for is 5/18/04.
 
Yes, Ron, I do want the answer to be either a subsequent Saturday or the Saturday if it happens to fall on one but NEVER the PREVIOUS SAturday as I cannot go backwards for the events.

And, yes...I will need to change the day from Saturday to Tuesday or Thursday as the date of departures for vessels varies from country to country.

Thanks so much for your continued help (and patience!)
Click to expand...

Well, I would use Bob Phillip's approach.

--ron
 
Back
Top