WORKDAY function not working in Excel 2007

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a schedule spreadsheet that calculates both future and past due dates
based on a mailing drop date. The formulas that are simple, this cell plus
that cell types, work fine. Any cells where the formula includes the WORKDAY
function are not calculating correctly.

I entered a formula into a different cell that uses the NETWORKDAYS function
to calculate the difference between the starting cell and the calculated cell
that is incorrect. In the formulas that it is supposed to take three days off
(-3) of the starting date, there is a NETWORKDAYS difference of 1042 between
the numbers. In the formulas that it is supposed to take five days off (-5)
of the starting date, there is a NETWORKDAYS difference of 1040 between the
numbers. In the formulas that it is supposed to take ten days off (-10) of
the starting date, there is a NETWORKDAYS difference of 1035 between the
numbers. These do form a pattern.

Then there are ones that don't fit the pattern. In the formulas that it is
supposed to take fifteen days off (-15) of the starting date, there is a
NETWORKDAYS difference of 1022 between the numbers. In the formulas that it
is supposed to take fifty-three days off (-53) of the starting date, there is
a NETWORKDAYS difference of 984 between the numbers.

I can't find any other mention of this problem on the boards. I have to be
able to work with this schedule, and may have to resort to having IT change
me back to Excel 2003 if I can't get it resolved quickly.
 
Laura

You are giving us no actual example of the formulas used just listing
NETWORKDAYS -15, etc.

You would be better to post an actual formula, what the cells in the formula
contain and the answer you are getting and what you expect to get.

Also you start by a subject of WORKDAY and then it changes to NETWORKDAYS,
which one is it?

Also be sure to use the DATE function for your date(s) as text dates can
produce unpredictable results

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/
 
I used NETWORKDAYS to do the oposite of WORKDAYS to figure out exactly how
far off the WORDAY function was. There is nothing wrong with NETWORKDAYS, in
my opinion.

Cell S7 contains the following formula: =WORKDAY(Q7,S$2,Holidays), the
result in the cell is 11/23/2010 [wrong]
Cell Q7 contains: Date(2006,12,11) (This was originally entered as text but
after I re-entered using the date format it didn't fix the result.)
Cell S2 contains: -13

The HOLIDAYS range includes: 1/1/2007, 5/28/2007, 7/4/2007, 9/3/2007,
11/22/2007, 11/23/2007 and 12/24/2007. I re-entered all of these in the date
format and it didn't change the result either.)

Here is another example that is a cell based on the result above.
Cell T7 contains the formula: =WORKDAY(S7,T$2,Holidays)...with a result of
11/19/2014 [wrong]
Cell T2 contains: -3

Thank you for looking at this.
--
Thanks,
Laura


Nick Hodge said:
Laura

You are giving us no actual example of the formulas used just listing
NETWORKDAYS -15, etc.

You would be better to post an actual formula, what the cells in the formula
contain and the answer you are getting and what you expect to get.

Also you start by a subject of WORKDAY and then it changes to NETWORKDAYS,
which one is it?

Also be sure to use the DATE function for your date(s) as text dates can
produce unpredictable results

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/
Click to expand...
 
Laura

Works fine here. I am wondering if you maybe have the international
settings screwed in windows and it is interpreting some of the dates wrong.
(UK or EU format dd/mm/yyyy), equally, are you using any 2 digit years that
may be being misinterpreted

I have it set up in Excel 2007 just as you describe and I get 11/22/2006

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

Laura said:
I used NETWORKDAYS to do the oposite of WORKDAYS to figure out exactly how
far off the WORDAY function was. There is nothing wrong with NETWORKDAYS,
in
my opinion.

Cell S7 contains the following formula: =WORKDAY(Q7,S$2,Holidays), the
result in the cell is 11/23/2010 [wrong]
Cell Q7 contains: Date(2006,12,11) (This was originally entered as text
but
after I re-entered using the date format it didn't fix the result.)
Cell S2 contains: -13

The HOLIDAYS range includes: 1/1/2007, 5/28/2007, 7/4/2007, 9/3/2007,
11/22/2007, 11/23/2007 and 12/24/2007. I re-entered all of these in the
date
format and it didn't change the result either.)

Here is another example that is a cell based on the result above.
Cell T7 contains the formula: =WORKDAY(S7,T$2,Holidays)...with a result of
11/19/2014 [wrong]
Cell T2 contains: -3

Thank you for looking at this.
Click to expand...
 
Back
Top