# Workday Function/Formula Ignoring Weekends

Aug 8, 2006

I'm at my wits end trying to make a workday formula work, but am having no luck. I've searched the other posts, but did not quite find a match. I'm trying to subtract a number of days from a workday function, but it seems to bypass ignoring the weekends (as is supposed to be done automatically with Workday). Here's the formula I'm trying to use:

=WORKDAY(H5,L5,' Reference Info'!\$E\$110:\$E\$117)-(I5/8)

I5/8 is a column with hours - this just calculates a number of days to subtract based on the number of scheduled hours.

The formula will calculate, but it still ignores weekends. I've tried burying the I5/8 within the parenthesis, moving it around in the formula,

## Excel 2010 :: Date Formula That Functions Like WORKDAY But Includes Weekends

Jun 24, 2011

Is there a formula similar to WORKDAY that would include weekends and make something due on the next business day? For example, I have a bill due on 6/1/11 and I need to follow up 5 days later - which would be normally 6/6/11 but the WORKDAY formula makes it 6/8/11 as it is adding 2 extra days.

I am using Excel 2010.

## =WORKDAY But Includes Weekends In The Specified Number Of Days

Nov 24, 2008

I'm looking for a function similar to =WORKDAY but with a slight variation.

I want the formula to return the date that is the closest non-weekend, non-holiday day that is a specific number of CALENDAR days from the start date. So the formula does not return weekend or holiday days, but does include them when counting the days away from the start date.

For example if the start date was Dec 1st, and I specified 14 days after this date, I would want Dec 15th returned (as the 14th is a weekend).

## Ignoring Weekends & Holidays - NETWORKDAYS

Dec 14, 2005

I have a schedule of holiday dates through year 2010 off to the side in cells N8-S18 that a workable formula could refer to.

........C...................D..................... ......E.........
1......1............10/25/2005...........10/26/2005
2......2............10/27/2005...........10/29/2005
3......0............10/29/2005...........10/29/2005
4......3............10/29/2005...........11/01/2005

Column C is the duration of the project in days. Column D is the project start date. Column E is the project end date.

As example, in D4 I'm using the formula =IF(C3=0,E3+0,E3+1). I need to include something to make it ignore weekends & holidays.

I don't have much experience in multiple conditions - I tried some things & made a decent mess of it.

## Time Difference Ignoring Weekends

Sep 6, 2007

provide me a formula to calculate the time difference in format [h]:mm:ss
The formula should ignore saturday and sunday.

Eg:

Fri 31-Aug-07 09:49

Mon 03-Sep-07 19:13

If I take the exact time difference, it is 81:24:37

But it should actually be 33:24:37, excluding 48 hours of weekend.

## WorkDay Function VBA

Jul 31, 2008

The function is to show the next workday date in cell a1

## Workday Function Use In VBA

Aug 15, 2006

I have a spreadsheet setup that uses the workday function. Shown as follows: =Workday(P\$2-1,V2,Z2:Z\$11)

P2 is a set date in which the location will not change. Z:Z is a list of Holidays. V2 should offset by 1 row. I want it when a command button is hit it runs that formula untill the rows value = "End". I have found "Working with Workdays" information at MSDN's website http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/workingwithworkdays.asp It seems to be for access but I'm trying to use in Excel and I am getting errors/confused. The main error I'm getting is Compile Error: Method or data member not found because of rst.FindFirst.

## Workday Function Or Similar

Jan 3, 2007

This is probably a simple question, but one I can't figure out after several hours. I am creating a spreadsheet focused on certifications.

I need to take the date each initial certification was achieved, and add x years to each cert, which is simple enough.

The sticky part is that the (date + x years) can't logically be a weekend; if it is, I need to subtract one or two days from the result, so that it's the *weekday* immediately prior.

e.g. if my initial certification date is: 1/23/2006 and my certifcation expires in four 4 years:

A47= date(2006,1,23)
h47= 10

=DATE(YEAR(A47)+H47,MONTH(A47),DAY(A47))

Result = Saturday, January 23, 2010

If the result is a Saturday or Sunday, I need to subtract one or two days from the date. In this example, I'd like: Friday, January 22, 2010 as the result.

I've been playing with 'if', et. al., to no avail.

## Function That Is The Equivalent Of WORKDAY But For Hours Instead

Mar 14, 2007

I am in need of some excel advice relating to date calculations. Basically I need a function that is the equivalent of WORKDAY but for hours instead. I have a series of events that take a certain length of time to complete, most of them less than a day but some more than. By way of example see the screenshot below:

In reality the last three operations would have to take place on the 27th of April, with the Welding operation starting on the end of the 25th around 7pm. The plant is running a 24 hour day, and works 5 days a week. How can I calculate the times in hours offset rather than going day by day? I need to account for

* Weekends
* Fixed Holidays
* Operations running as seamlessly as possible

Any advice welcome. I have attempted to use WORKDAY with the number of days to deduct rounded to the nearest day and then subtracting the operation time but this results in errors where operations would cumulatively go over a working day. The objective is by knowing when the end product is needed and knowing how long each operation takes it is possible to discover when to start manufacture. VBA or Formula code is fine as this will be integrated into a VBA project.

## WORKDAY Function To Calculate The Start Date

Jun 11, 2009

As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends?

For example,

Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009

Start Date: ???
Finish Date: 8 Jun 2009
Duration: 5 days

The Start Date should be 30 May 2009.

Seems like I am not able to use WORKDAY function to calculate the start date.

## Force Formulae Recalculation & Return Day Name From Workday Function

Feb 27, 2008

I'm trying to make a schedule of deposits made for the month of March 2008. (Deposits are made on a daily basis.) With this, I wanted to know when the deposit would clear with the bank using the WORKDAY formula given that I have to count 6 banking days after the date of deposit (Saturdays and Sundays excluded).

I'm using Mac and I recently upgraded to Office 2008 (never tried WORKDAY formula in other versions). I am aware of the syntax used for this formula and it works just fine in other cases. But I noticed that with start_dates falling on a Thursday or Friday of the week, Excel would give me a result date that falls on a Sunday, which is odd given that it's supposed to ignore non-working days or weekends. (I haven't even gotten to inputting holidays yet.)

## Workday Formula On A Conditional Formatting

May 29, 2009

I am having an issue, because aparently workday formula does not work in conditional formattings.
I have the two formulas below to use in conditional formattings, but I can't figure out how to re-write them so that Excel will allow me.

## Workday And Network Day Formula Challenge

Feb 11, 2007

I am trying to create a sheet for a project that will identify dates a project will conclude omitting weekends and holidays. I keep getting an error with the formula I have.

A1 is the start date
B1 is the number of days after the start date.

The formula I am using:
=WORKDAY(A1,NETWORKDAYS(A1,A1+B1),{""4/6/2007","5/28/2007", "7/4/2007","9/3/2007","11/22/2007","12/25/2007","1/1/2008","1/21/2008","3/21/2008","5/26/2008", "7/4/2008","9/1/2008","11/27/2008","12/25/2008"}"}+0)

The error I get is with the parens (A1,A1+B1). - at least the parens are highlighted in purple.

## Formula That Would Calculate What Workday A Date Would Be

Feb 15, 2007

I am trying to find a formula that would calculate what workday a date would be.

So if the date is 2/19/2007, I want to know that is the 13th workday.

## WORKDAY Formula Replacement W/o Use Of Analysis Add-In

Jun 17, 2008

My boss has made it clear I can't require our people to use the add-in.

I have searched under WORKDAY as well as under Barry Houdini, since someone said he has come up with plenty of replacements for the Analysis add-in... but I am not finding it.. can someone help?

Using the add-in, this is what I have:

myCell.FormulaR1C1 = "=WORKDAY(RC[1],-RC[2])"

## Date Function That Avoids Weekends

Sep 1, 2009

I just want to ask if we can create a formula that will not go to saturdays and sundays. example :

I use =today()+3 so it means if today is tuesday the result is friday, if im going to open it tomorrow the result will adjust to saturday if that happens i want the day to be automatically go back on friday, the date itself, so it means i'll just wait on friday to get the result as "monday". It just avoid saturdays and sundays,. Is that possible? By the way, format would be m/d/yyyy.

## Rank Function Ignoring Only Zero Values

Sep 12, 2013

I'm trying to rank the values in row 94 while ignoring only zero values and including positive and negative values. I'd like the function to rank the values in descending order, beginning with the most positive value and ending with the most negative value while ignoring all zeros. The function I've come up with so far ranks the positive values correctly and ignores the zeros, but the negative values are ranked as if they come after all of the zero values instead of being ranked immediately after the smallest positive value. Is there a way to fix the function below to make it do what I want?

=IF(D94=0,"",RANK(D94,\$D\$94:\$AY\$94,0))

## Add Additional Formula To Existing Formula To Exclude Weekends And Holidays?

Mar 27, 2014

I have an existing formula in a cell linking back to a start date only. determining how to add an additional formula to exclude weekends, and possibly holidays.

## Ignoring Empty Lines And Still Having AutoFilter Function

May 30, 2008

I'm building a sales tracking list and I have empty rows between each entry to allow easier viewing of the data. When I add the empty row to act as a "visual space" between line entries, auto filter only sees the first line of text and not all 400 rows separated by empty rows.

## Exclude Weekends And Holidays From A Formula.

Aug 27, 2009

I have been asked to create a worksheet to calculate the percentage of the utilisation of a number of loan cars per month. Each of the cars may be used on each and every day of the week but the percentage utilisation is to be calculated on working days only. However it is essential to record the name of the driver of the car at weekends and holidays. Column A is populated with the dates of the month. Colum B is populated with the relative days of the week. In column C and subsequent columns I record the name of the driver of a particular car registration. Is there any way that I can count the number of drivers of each car on working days only and not weekends or bank holidays?

## Formula To Exclude Weekends (Saturday And Sunday)

Aug 4, 2014

I have below formula which gives me the due status.

[Code] ......

How can I modify the code so it excludes Saturday and Sunday.

## Ignoring #DIV/0! In Formula's

Dec 10, 2009

I have rows of data with numbers and these #DIV/0! errors. I need a sum formula so when going through the entire row it will ignore these errors, at the moment as they are included in my sum formula the total is also giving me #DIV/0!

## Formula For Working Out Number Of Days Between Dates Excluding Weekends?

Aug 6, 2012

I want to create a formula that works out the number of days between two dates but excludes weekends?

## Ignoring Characters While Formula Looks For Value?

Apr 16, 2014

I have stacked formulas that look for values via vlookup and index match functions.

Sometimes the worksheet will have ( 100 ) separated into 3 columns (open parenthesis, number, closed parenthesis) and the formula will pull the first column containing the parenthesis. How can I write the formula to ignore the "(" and get to the "100"?

The formula uses ISERROR to move forward, could I incorporate ISNUMBER also or is there a better way to do this?

## IF And SUM Formula But Ignoring Text

Mar 31, 2009

I have the following formula but I want to ensure that the sum ignores any text so I don't get #VALUE! returned. I've tried including ISERROR but doesn't seem to be working correctly. The formula is: =IF(H19+H20+H21+H22+H23+H24+H25+H26+H27+H28>0,SUM(H19:H28),"")

## Ignoring Errors In DAVERAGE Formula

Jun 10, 2014

how to ignore errors when using DAVERAGE?

## Ignoring Negative Results In A Formula

Jun 9, 2009

Ignoring Negative results in a formula
Question withdrawn

## Ignoring Blank Cells In Formula

Feb 5, 2010

I am trying to return the criteria MET, NOT MET & EXCEEDS. This works when each cell contains a number, but when one is blank the formula below returns "EXCEEDS" I'm guessing this is because is sees a blank as zero.

=IF(BN102<\$BJ102,"NOT MET",IF(BN102>\$BJ102,"EXCEEDS",IF(BN102=\$BJ102,"MET")))

I've tried using this alternative but it still returns the same.

=IF(BO102<\$BJ102,"NOT MET",IF(BO102>\$BJ102,"EXCEEDS",IF(BO102=\$BJ102,"MET",IF(ISBLANK(B102)," ",""))))

Basically I need to know how to make excel ignore the blank cell and not see it as zero thus returning "EXCEEDS". Although the cell values are integers they are derived from another cell using the following formula, I'm not sure if this makes any difference

=IF(ISERROR(VLOOKUP(BO11,\$BH\$63:\$BI\$87,2,FALSE)),"",(VLOOKUP(BO11,\$BH\$63:\$BI\$87,2,FALSE)))

## Array Formula Not Ignoring Blanks To Count Row Number?

Oct 3, 2012

I have this array formula

=IF(ROWS(A\$11:A13)1,IF(MSB!\$A13"",SMALL(ROW(Table1[APP DATE])-ROW(MSB!\$A\$11)+1,ROWS(A\$11:A13)),""))))

the problem is that when both if statements are true i want it to get the row number, this is because table1 has blank rows (can not be avoided as the data is linked from a closed workbook)

i can see this works up until it meets the first blank row and i get #value error.

on table1 data exists on row 1,2,5 and 8 so my problem is getting the small function k to report these numbers?

## Conditional Format If Not Workday

May 26, 2009

Maybe I'm getting a blank, maybe the answer is so easy, but lately I canīt solve this: How do I use conditional format to change cell color if cell value (date) is not workday?