Returning next day (including weekends but excluding holidays)

**cathyfirst** · 07-25-2019, 06:08 AM

Hi

I am relatively new to the forum, and am usually OK with formulas but this one is causing me no end of problems.
I was looking at another post that was similar and used workday.intl, but couldnt get it to work.

Scenario
- Maintenance Inspections are carried out every 4 weeks. I need to show both the next actual date and a proposed date in case it is a public holiday. I can get the next date returned ok, but if it is a holiday I want it to move to the next working day.

The formula I have used is =WORKDAY.INTL(G4,0,"0000000",PublicHols)
G4 is my first date
Publichols is a named range for public holidays
I need the next date to be in J4

If I use =WORKDAY.INTL(G4,1,"0000000",PublicHols) it works, but adds on a day - which isnt what I want. I only want to add days if it is a public holiday

Sorry for the long post

**AliGW** · 07-25-2019, 06:20 AM

See if this works:

=WORKDAY(G4,1,-1,PublicHols)

**cathyfirst** · 07-25-2019, 06:23 AM

thanks Ali, but unfortunately that didnt work - it returned an error "You've entered too many arguments for this function"

**AliGW** · 07-25-2019, 06:26 AM

That's odd, as I have used something similar before.

Please attach a sample workbook (desensitised)

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**AliGW** · 07-25-2019, 06:30 AM

What does this give you?

=WORKDAY(G4,1,PublicHols)

**cathyfirst** · 07-25-2019, 06:34 AM

Workbook attached - thanks for helping

**cathyfirst** · 07-25-2019, 06:35 AM

Hi

=WORKDAY(G4,1,PublicHols) returns the next day, but I only want this if it is a public holiday, if it isnt I want to preserve the original date

**AliGW** · 07-25-2019, 06:38 AM

We need your workbook with the holiday range in it, please.

**AliGW** · 07-25-2019, 06:42 AM

The named range is not correct - it is missing the first date - so try this:

=IF(ISNA(MATCH(G4,$T$5:$T$33,0)),G4,G4+1)

**cathyfirst** · 07-25-2019, 06:49 AM

Thank you AliGW, but it doesnt work. If I change the date in G4 to 25th December 2019 the formula returns the date of 26th December 2019 which is in the 'list' of holidays. I think we need to use a variation of

=WORKDAY.INTL(G4,1,"0000000",PublicHol)

**AliGW** · 07-25-2019, 06:51 AM

OK - but the problem is you are drip-feeding information. We can only offer solutions based on what you tell us, not based on what you don't tell us.

Have you fixed your named range?

**cathyfirst** · 07-25-2019, 06:56 AM

Hi
I have changed the daterange. I will attache the updated file.

In my original post I mentioned that I wanted to add days if it was a public holiday

**cathyfirst** · 07-25-2019, 06:57 AM

updated file attached

**AliGW** · 07-25-2019, 07:06 AM

Yes, you said you wanted to add a day if it was a public holiday. However, at no point have you provided us with an indication of your expected outcomes.

Please provide a sample data set with JUST December dates. Instead of the column with the failing formula, manually type in the dates you want returned. Thanks.

**cathyfirst** · 07-25-2019, 07:35 AM

HI

I have attached the updated spreadsheet and included dates for August and December

I have also added columns to show the date with the formula, the expected date and a comments column

**AliGW** · 07-25-2019, 07:45 AM

I think this does it:

=IF(ISNA(MATCH(G4,PublicHols,0)),G4,WORKDAY(G4,1,PublicHols))

**cathyfirst** · 07-25-2019, 07:49 AM

That works - thank you so much ...

**cathyfirst** · 07-25-2019, 07:50 AM

virtual boquet of flowers on its way to you (and a bottle of wine)

**AliGW** · 07-25-2019, 07:50 AM

Not so fast!

I think it might exclude weekends, but this one should work:

=G5+2-NETWORKDAYS.INTL(G5,G5+1,"0000000",PublicHols)

PS Elderflower spritzer, please - I'm TT!!!

**cathyfirst** · 07-25-2019, 08:16 AM

Elderflower spritzer on its virtual way (that would be so cool and refreshing in this heat).

Unfortunately the revised formula doesnt work in row 6 (24th december should return 24th december but returns 25th december which is in the list) strange because all other dates seem to work.

**AliGW** · 07-25-2019, 08:21 AM

OK - let's try this, then:

=IF(ISNA(MATCH(G5,PublicHols,0)),G5,G5+2-NETWORKDAYS.INTL(G5,G5+1,"0000000",PublicHols))

**Phuocam** · 07-25-2019, 08:34 AM

One way:

=WORKDAY.INTL(G4-1,1,"0000000",PublicHols)

**AliGW** · 07-25-2019, 08:56 AM

I think that's what I was trying to do before! However, it doesn't work as requested for Sat 28 Dec, which should come out as Mon 30 Dec, if I have i=understood correctly.

**BlindAlley** · 07-26-2019, 12:32 PM

I think this one works
Using named ranges 1 for actual holiday dates and one for adjusted work dates.

Please Login or Register  to view this content.

It uses a Perpetual Public Holiday Calendar for the holiday dates and adjustments for New Year and Christmas.

Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Re: Returning next day (including weekends but excluding holidays)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Re: Dates not including weekends and holidays

Bookmarks

Bookmarks

Posting Permissions