date and time [skipping weekends]

[rhhince]

I have a number in a cell that represents days. I wish to add these
days to the current date and time, which is easy to do. =NOW()+A3
I format it into time displaying date and time.
How can I automatically skip the weekend from Friday 16:00 to Sunday
16:00 which is 48 hours in the addition?

 

[Sandy Mann]

Try:

=NOW()+A3+CHOOSE(WEEKDAY(NOW()+A3,2),0,0,0,0,0,2,1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Lars-Åke Aspelin]

I have a number in a cell that represents days. I wish to add these
days to the current date and time, which is easy to do. =NOW()+A3
I format it into time displaying date and time.
How can I automatically skip the weekend from Friday 16:00 to Sunday
16:00 which is 48 hours in the addition?

Please specify you problem with more details.
What do you mean by "skip the weekend ... in the addition"?

Do you want the result to be "rounded" up to Sunday 16:00 if the
result of NOW()+A3 falls within the "weekend"?
Or do you want to just add 2 days to the result in that case?
And what if A3 is more than 7 (one week), e.g 50?
Do you want to add 2 days for each weekend covered by the A3 days?

/ Lars-Åke

 

[Ivyleaf]

Hi,

This is assuming that you want to add x days to the date in cell A5
(replace with Now() if necessary) and assuming that number of days to
add is in cell D5.
It also assumes that I understand your question correctly... as I see
it you want to add a number of days and skip and weekends. ie. If it's
Thursday and you want to add 4 days, you would expect the result to be
Wednesday the following week and so on.

=A5+IF(D5>5-WEEKDAY(A5,2),INT((D5+WEEKDAY(A5,3))/5)*2+D5,D5)

There is a good chance that this can be optimised... haven't looked at
that yet. The one thing to be aware of is that this formula will
glitch if the date you are adding to is in fact a weekend... in which
case you might need to wrap another if around it to test for that.

Cheers,
Ivan.

 

[Rick Rothstein \(MVP - VB\)]

Give this function a try...

=WORKDAY(NOW(),A3)

Note 1: If this function is not available, and returns the #NAME? error,
install and load the Analysis ToolPak add-in.

Note 2: Also check out the help files for this function as there is an
optional 3rd argument which can allow you to skip over holidays as well.

Rick

 

[rhhince]

This worked very well. Thank you!
The reason I wanted this function is I trade forex and my spreadsheet
formulas suggest trading time frames which do not include weekends,
therefore I need to skip weekends as there is no trading. So, for
example, if a signal came on a Thurs. indicating that a change in 3
days could occur, under normal counting that would be on Sunday. I
have to skip weekends, therefore the signal should come on Tues.
Anyways, it works well and gives a better idea without having to look
at a calendar.
Thanks again.

 

[rhhince]

This worked very well.

=NOW()+A3+CHOOSE(WEEKDAY(NOW()+A3,2),0,0,0,0,0,2,1)

 

[Ivyleaf]

Hi Rhince,

Although Sandy's formula will in some respect skip some weekends, I
would advise some caution with it. Since you have explained your
situation a little more clearly now, testing the formula with your
exact example (Thurs + 3 working days) actually gives me Monday the
following week (only 2 days by me way of thinking), as will adding 2
days or four days in fact. Just a word or warning.

Cheers,
Ivan.

 

[rhhince]

Upon further scenarios, this formula worked the best.
=A5+IF(D5>5-WEEKDAY(A5,2),INT((D5+WEEKDAY(A5,3))/5)*2+D5,D5)