dragging only workdays
- Thread starter ver
- Start date
Assumptions:
First date is manually entered into A2. List of non-working days is entered into B2:B7 as the the full name for days of week (e.g., Friday, Saturday, Sunday).
Formula in A3:
=A2+MIN(IF(ISNA(MATCH(TEXT(A2+ROW($E$1:$E$7),"dddd"),$B$2:$B$8,0)),ROW($E$1:$E$7)))
This is an array formula, confirm using Ctrl+Shift+Enter, not just Enter.
The cell reference E1:E7 within the ROW function can be any column, it simply needs to reference rows 1:7. The B2:B8 is where user inputs non-working days.
First date is manually entered into A2. List of non-working days is entered into B2:B7 as the the full name for days of week (e.g., Friday, Saturday, Sunday).
Formula in A3:
=A2+MIN(IF(ISNA(MATCH(TEXT(A2+ROW($E$1:$E$7),"dddd"),$B$2:$B$8,0)),ROW($E$1:$E$7)))
This is an array formula, confirm using Ctrl+Shift+Enter, not just Enter.
The cell reference E1:E7 within the ROW function can be any column, it simply needs to reference rows 1:7. The B2:B8 is where user inputs non-working days.
Hi, tnx for the answers, I learned a lot. But, it still didn't solve my problem.
I just wanted to write a date, for example: 29/02/12
and then to drag it down, missing Fridays and Saturdays.
While the only option I found was without Saturdays and Sundays.
I want the table to be:
Wedn 29/02/12
Thur 1/03/12
Sun 4/3/12
Mon 5/3/12
Tue 6/3/12
Wedn 7/3/12
Thur 8/3/12
Sun 11/3/12
etc.
I don't need the days names, it was only to make it clearer.
tnx
I just wanted to write a date, for example: 29/02/12
and then to drag it down, missing Fridays and Saturdays.
While the only option I found was without Saturdays and Sundays.
I want the table to be:
Wedn 29/02/12
Thur 1/03/12
Sun 4/3/12
Mon 5/3/12
Tue 6/3/12
Wedn 7/3/12
Thur 8/3/12
Sun 11/3/12
etc.
I don't need the days names, it was only to make it clearer.
tnx
Hi Luke
This is the Help for Workday.intl
I'm sure you'll like the flexibility it now offers
WORKDAY.INTL function
Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. Weekend parameters indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.
Syntax
WORKDAY.INTL(start_date, days, [weekend], [holidays])The WORKDAY.INTL function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
Start_date Required. The start date, truncated to integer.
Days Required. The number of workdays before or after the start_date. A positive value yields a future date; a negative value yields a past date; a zero value yields the start_date. Day-offset is truncated to an integer.
Weekend Optional. Indicates the days of the week that are weekend days and are not considered working days. Weekend is a weekend number or string that specifies when weekends occur.
Weekend number values indicate the following weekend days:weekend-number Weekend days
1 or omitted Saturday, Sunday
2 Sunday, Monday
3 Monday, Tuesday
4 Tuesday, Wednesday
5 Wednesday, Thursday
6 Thursday, Friday
7 Friday, Saturday
11 Sunday only
12 Monday only
13 Tuesday only
14 Wednesday only
15 Thursday only
16 Friday only
17 Saturday only
Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. 1111111 is an invalid string.
For example, 0000011 would result in a weekend that is Saturday and Sunday.
Holidays Optional. An optional set of one or more dates that are to be excluded from the working day calendar. Holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates. The ordering of dates or serial values in holidays can be arbitrary.
This is the Help for Workday.intl
I'm sure you'll like the flexibility it now offers
WORKDAY.INTL function
Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. Weekend parameters indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.
Syntax
WORKDAY.INTL(start_date, days, [weekend], [holidays])The WORKDAY.INTL function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
Start_date Required. The start date, truncated to integer.
Days Required. The number of workdays before or after the start_date. A positive value yields a future date; a negative value yields a past date; a zero value yields the start_date. Day-offset is truncated to an integer.
Weekend Optional. Indicates the days of the week that are weekend days and are not considered working days. Weekend is a weekend number or string that specifies when weekends occur.
Weekend number values indicate the following weekend days:weekend-number Weekend days
1 or omitted Saturday, Sunday
2 Sunday, Monday
3 Monday, Tuesday
4 Tuesday, Wednesday
5 Wednesday, Thursday
6 Thursday, Friday
7 Friday, Saturday
11 Sunday only
12 Monday only
13 Tuesday only
14 Wednesday only
15 Thursday only
16 Friday only
17 Saturday only
Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. 1111111 is an invalid string.
For example, 0000011 would result in a weekend that is Saturday and Sunday.
Holidays Optional. An optional set of one or more dates that are to be excluded from the working day calendar. Holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates. The ordering of dates or serial values in holidays can be arbitrary.
