Is this impossible?

[intheskywithdiamonds]

People always ask 'can i add x amount of days to a date excluding weekends/holidays?'

I want to know;

Can I add x amount of days to a date, excluding specific days of the week i.e mondays and tuesdays?

So if I want to add 20 days to a date but not count mondays or tuesdays? Is that possible?


Please help me!

All input appreciated

 

[Jonmo1]

Welcome to the board.

Which version of Excel do you have?
It's pretty easy in XL2010

=WORKDAY.INTL(A1,10,"1100000")

The string "1100000" represents each day Monday through Sunday.
1 means the day is excluded
0 means the day is counted.

 

[Firefly2012]

If you have at least xl2010 then you can use WORKDAY.INTL:

Excel 2010
	A	B
1	25/02/2013	24/03/2013
Sheet1

Cell Formulas
Range	Formula
B1	=WORKDAY.INTL(A1,20,3)

Check Excel Help for the third argument options

 

[intheskywithdiamonds]

Hello guys, thanks for responding

I have excel 2010.

The issue is the excluded days will be based on an input work pattern

Is it possible to exclude days that are based on a range of cells (which will change based on selected working days)?

Sorry if this is too complicated or confusing.

I can attach my spreadsheet if necessary?

 

[Jonmo1]

Can you be specific?
How EXACTLY should the formula know which days to include/exclude ?

 

[intheskywithdiamonds]

The user selects their working days based on a 'Yes/No' drop-down list.

So if they select 'No' beside Tuesday, that means Tuesday should be excluded

 

[intheskywithdiamonds]

Here is an overly detailed overview of my project that no one responded to if you want a bit of background knowledge!

http://www.mrexcel.com/forum/excel-...ing-certain-days-week-i-e-monday-tuesday.html

This one’s tricky. You have to use imaginary numbers, like eleventeen..

 

[Jonmo1]

Can you be more specific please..
So you have 7 cells with yes or no..
Which 7 Cells? Are they in E1:K1 ?? Does E1=Sunday F1=Monday etc..
Which cell contains the beginning date?
Which cell contains the number of days to add?


Yes this can be done, but not easily. That's why we need the specifics.
and it will require the use of "Helper" cells.

 

[intheskywithdiamonds]

Ok so if the user selects 'No' next to Monday, the cell next to 'No' populates with the number '2' - in line with the excel standard of 2= Monday

And if they select 'No' next to Tuesday, the cell next to 'No' populates with the number '3' - and so forth

I have this range names as ExcludeDaysOfWeek, so excel recognises these numbers as corresponding to the days that have to be excluded, but i'm not even sure if i can use the range

 

[Jonmo1]

Here is an example of how I would do it.

The user selects their days worked in F2:F8. Yes = working, No = Not Working
The week MUST be in Monday through Sunday fassion.
The data can be entered in F2:F8 in any order you like, but the formula in G10 MUST go in order from Monday to Sunday.

Excel Workbook
	A	B	C	D	E	F	G
1	Start Date	Number to add	Result		Pattern
2	2/25/2013	10	3/11/2013		Sunday	Yes	0
3					Monday	Yes	0
4					Tuesday	No	1
5					Wednesday	Yes	0
6					Thursday	Yes	0
7					Friday	No	1
8					Saturday	Yes	0
9
10							0010010
Sheet1