Weekday Still Calculates the Weekend

[BarnieFife]

I am trying to omit weekends and holidays from my timeline calculations, but I my formula is still counting the weekends for some reason. Any help would be greatly appreciated:


=IF(WEEKDAY(G21)=7,WORKDAY(G21,1,E31:E40),IF(WEEKDAY(G21)=1,WORKDAY(G21,1,E31:E40),WORKDAY(G21-1,1,E31:E40)))

(My holidays are listed between E31:E40)



Thank you in advance

[hrlngrv]

I can't find any errors in WORKDAY.

Your formula could be approximated as

=IF(G21 is Saturday,WORKDAY(G21,1,E31:E40),IF(G21 is Sunday,WORKDAY(G21,1,E31:E40),WORKDAY(G21-1,1,E31:E40)))

which could be condensed to

=WORKDAY(G21-(WEEKDAY(G21,2)<6),1,E31:E40)

which means that if G21 is a weekday (M-F), then this formula should return G21 except when G21 appears in E31:E40 as a holiday. Is that the problem? If so, there may be a problem in the actual contents in E31:E40. GUESSING: spaces could be HTML non-breaking spaces, ASCII hyphens (decimal character code 45) could be HTML dashes (various character codes).

Which is long-winded way of saying you need to provide your workbook, or at least a copy with the actual contents in G21, one cell with the formula NOT working, and your actual E31:E40. You can clear everything else. Dates in G21 and E31:E40, one erring formula, and NOTHING ELSE shouldn't be proprietary information.

[bebo021999]

I think
=WORKDAY(G21,1,E31:E40)
is enough.
If G21 is from Mon-Thu, it returns next day Tue-Fri
If G21 is from Friday-Sunday, it returns next Monday

[BarnieFife]

It works in the sense that it pushes the day to the following Monday, but I don't want to include Saturday and Sunday in the timeline - only business days (M-F). So, counting three days from Friday, should land me on Wednesday of the following week. Any idea on how I can tweak it?

[bebo021999]

So

=WORKDAY(G21,3,E31:E40)

will count 3 working days from Fri, and returns next Wednesday

[hrlngrv]

If you want 3 workdays from Friday 9 Oct 2020 (G21) with 12 Oct 2020 a holiday (Columbus Day in US) included in E31:E40,

=WORKDAY(G21,3,$E$31:$E$40)

returns Thursday 15 Oct 2020 because 12 Oct 2020 was included as a holiday. If that Monday weren't a holiday, the formula above would return Wednesday 14 Oct 2020. It does on my machine.

[BarnieFife]

I see what I did there - I had it counting 1 day like a dope. Thank you all for your help.

[BarnieFife]

What if I wanted the number of days to be variable, depending on a user input in a different cell? Still only count working days, but it might be 5 or 6 sometimes.

[hrlngrv]

=WORKDAY(FromDate,NumberOfDays,HolidaysList)

so if the number of workdays were in X99,

=WORKDAY(G21,X99,E31:E40)

[BarnieFife]

Last question - what if I wanted to count weekend days (not holidays) for any timeline requiring 6 or more days out?

5 days or less = do not count weekends or holidays
6 days or more = OK to count weekends but no holidays (but it can't end on a weekend day)

Thank you again

[hrlngrv]

If you want 5 days from G21 whether or not that day were a weekend day or holiday, =G21+5.

If you want 5 days from G21 skipping weekend days but the result could be a holiday, =WORKDAY(G21,5).

If you want 5 days from G21 skipping holidays but the result could be a weekend day, =WORKDAY.INTL(G21,5,"0000000",E31:E40).

For anything more complicated, look into the WORKDAY.INTL function.

[BarnieFife]

I have attached a sample sheet as well.

For tasks that take 5 days or less, I CAN'T count weekend days or holidays.
For tasks that take 6 days or more, I CAN count weekend days and holidays, but the completion date CAN'T land on a weekend day or holiday.

The attachment shows the start date in blue (G7) and the number of days in blue (C8,C9,C10), the result will populate the red dates. The blue cells are user inputs, so they will be varied. The list of holidays is listed at E22:E31.

I have two formulas that are working great so far:

For tasks that take 5 days or less, I am using:

=IF(G7="","",WORKDAY(G7,C9,E22:E31))

For tasks that take 6 days or more, I am using:

=IF(G7="","",IF(WEEKDAY(G10)=7,WORKDAY(G10,1,E22:E31),IF(WEEKDAY(G10)=1,WORKDAY(G10,1,E22:E31),WORKDAY(G10-1,1,E22:E31))))


Sometimes tasks might take longer than 5 days. Is there a way to combine these formulas into one, so when the user inputs the number of days less than 5 or more than 6, it will automatically count the correct days and holidays?

[Bo_Ry]

Please try

=WORKDAY(WORKDAY.INTL($G$7,C8,IF(C8>5,"0000000",1),IF(C8>5,0,$E$22:$E$30))-1,1)

[hrlngrv]

I've had difficulty interpreting what you mean by count. It wasn't clear to be whether you mean include or exclude.

If you mean when the number of days is 5 or less, weekend days aren't workdays, but for higher values weekend days would be included as workdays, and in all cases holidays aren't workdays, then I believe your D8 formula could be

Please Login or Register  to view this content.

which could be filled down into D9:D15.

For me that produces the following results.

	C	D	E	F	G
7				Friday	11/06/2020
8	2	Tuesday	11/10/2020
9	5	Monday	11/16/2020
10	10	Tuesday	11/17/2020
11	12	Thursday	11/19/2020
12	20	Monday	11/30/2020
13	30	Wednesday	12/09/2020
14	30	Wednesday	12/09/2020
15	30	Wednesday	12/09/2020

By row,

8) skip Sa Su, so Mo Tu is 2 workdays later.

9) skip Sa Su, We 11/11/2020 from E28, Sa Su, so Mo Tu Th Fr Mo is 5 workdays later.

10) skip only We 11/11/2020 from E28, so Sa Su Mo Tu Th Fr Sa Su Mo Tu is 10 workdays later.

11) no holidays between 10 and 12 workdays after 11/06/2020, so (continuing 10) We Th 2 days after result from (10), so 12 workdays later.

12) skip We 11/11/2020 from E28 and Th/Fr 11/26-27/2020 from E29:E30, so Sa Su Mo Tu Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Sa Su Mo is the next non-holiday weekday from 20 days after 11/06/2020.

13-15) skip We 11/11/2020 from E28 and Th/Fr 11/26-27/2020 from E29:E30, so Sa Su Mo Tu Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Sa Su Mo Tu We Th Fr Sa Su Mo Tu We is 30 workdays later.

[BarnieFife]

Thank you Bo_Ry - it worked perfectly!