Help with Excel Weekday Logic 1

[Mike555]

Hi. I have the simple Excel function =Today+E4 which increments today's date by the amount specified in cell E4. I'm trying to figure out how to exclude weekend dates from this formula.

For example...
If Today() = 04/01/2005(Friday) and cell E4 = 1, then currently the function would calculate a value of 04/02/2005(Saturday). However, I'd like the function to skip weekend days so that in this example the end result would actually be 04/04/2005(Saturday).

Is this possible? I've tried playing with the NETWORKDAYS and WORKDAY functions but I can't seem to figure this out. Any suggestions? Thanks!

--
Regards,
Mike

 

[byundt]

Mike,
Try a formula like this:
=TODAY()+E4+CHOOSE(WEEKDAY(TODAY()+E4,2),,,,,,2,1)

The CHOOSE part of the formula adds a 2 if the result was Saturday, a 1 if it was SUNDAY, and a 0 for Monday through Friday.
Brad

 

[TonyJollans]

Hi Mike,

What is your problem wih using
[blue][tt] =WORKDAY(TODAY(),E4)[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Kurt111780]

THis does not work in excel 2003. I get name error on workday.

=WORKDAY(TODAY()+C33)
or
=WORKDAY(TODAY(),C33)

This does not work either:
=WORKDAY()

Any suggestions?

I want to increment the day by one day from today but skip weekends.

Thanks,
Kurt



It's only easy when you know how.

http://www.seedandgarden.com

 

[byundt]

Kurt,
The syntax should be:
=WORKDAY(TODAY(),C33)
=WORKDAY(TODAY(),C33,Z1:Z10) where Z1:Z10 contains a list of the dates of your company's paid holidays

Do you have the Analysis ToolPak add-in installed? You need it for the WORKDAY function.

Make sure that the checkbox is checked for Analysis ToolPak in the Tools...Add-Ins menu item. If you don't see it listed, you need to use the Add/Remove Programs control panel from the Start menu to reinstall the Analysis ToolPak.
Brad

 

[Kurt111780]

Hi Brad:

Thanks for the help. I had to turn on Analysis ToolPak.

This works great now.
=WORKDAY(TODAY(),A8)

How would I make it start on the first day of the current month rather than today?

Thanks,
Kurt

It's only easy when you know how.

http://www.seedandgarden.com

 

[xlbo]

You would need to create a date using TODAY()

as a starter for 10, MONTH(TODAY()) will return the current month number an "01/" will mark day 1. Have a look at the other date functions and the & / Concatenate function for the rest

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

There are lots of ways to get to the first day of the month. I like this one:
[COLOR=blue white]=today()-day(today())+1[/color]

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

This is another good one:
[COLOR=blue white]=eomonth(TODAY(),-1)+1[/color]

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

ooooh - like that one John - I have always just "built" a date to do this kinda thing - have a purple pointy thing

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[Kurt111780]

Thanks guys,

Here is the my code now.

=WORKDAY(TODAY()-DAY(TODAY())+1,A8)

Works great.

Kurt

It's only easy when you know how.

http://www.seedandgarden.com