Excel 2003 different results using similar functions

[dawnd3]

Hi there, I am using NETWORKDAYS Funtion to determine how many workdays there are between two dates and the WORKDAY function to determine the end date when I put in a beginning date and the number of days. Using the same holiday list, each will result in different answers, but only sometimes. I put -1 after the WORKDAY function and that help with many of the mis-calculations but then occasionally it was still a day or more off. If you look at my example, attached, you can see that it is a few days off. (I have exposed the formula below each section so that you can see what I did in the gray cells.)

Thanks for any help,

DAwn

http://www.calliopeconsulting.net

 

[SkipVought]

Hi,

Is there a question in there?

If ther is, please post values for each argument you are using for each function.

Please also post the holiday date list that you are using in your holiday range argument.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[dawnd3]

Skip, did you look at the attachment? My question is, why is NETWORKDAYS and WORKDAY counting the days differently. I want to add that it appears that WORKDAY formula is the one that is messing up. IF you look at the attachment you will see the exact spreadsheet with the formulas that I used showing below each cell that is calculated. The holidays are at the bottom of the page in the sample.

Thanks,

Dawn

http://www.calliopeconsulting.net

 

[SkipVought]

You posted a pdf not a workbook.

You posted inconsistent arguments in your pdf.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[SkipVought]

Check and see how NETWORKDAYS calculates the number of days between 1/5/2009, a Monday, and 1/6/2009, a Tuesday.

Then see how WORKDAYS works adding days.

Then adjust your logic & expectations accordingly.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[dawnd3]

Skip, you are too smart for me, I don't know what you mean by "adjust your logic & expectations accordingly". If I do the dates you suggested, NETWORKDAYS says it is "2" days and WORKDAY says it is "1" day. The PDF I sent is an exact screen shot from my spreadsheet. Since you can't see the formula in a screen shot, I copied them in the cell below as text. Is there something else you need to see that you can't? I can send you the spreadsheet if you want.

Thanks a bunch,

Dawn

http://www.calliopeconsulting.net

 

[dawnd3]

P.S. Also please tell me what is inconsistent in my arguements? I added the -1 to the one because it helped it to be consistent with the other one more often. I just can't figure out why one is sometimes a day off from the other and sometimes it is not, when I use the same start date and try to make the amount of days match and end date match.

http://www.calliopeconsulting.net

 

[SkipVought]

The Holiday range was not the same number of cells as the dates that you posted, but as it turned out, it did not matter.

Can you see how NETWORKDAYS and WORKDAYS are calculating? There's is a one day difference. One or the other is not necessarily wrong. They are just differrent.

So you subtract a day, knowing exactly why it has to be done.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[SkipVought]

You have to read the HELP carefully.

NETWORKDAYS returns the number of WHOLE working days between two dates.

So if the start date is 1/5/2009 and the end date is 1/5/2009, NETWORKDAYS returns 1, cuz, ONE WHOLE WORKING DAY has been referenced.

WORKDAY adds a day to the start date, so you can see the difference in the results right away.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[dawnd3]

Well I thought that I did account for that skip by subtracting a 1 from the WORKDAY Function, however, it was still off. If I could send you the spreadsheet and the exact dates that were off, even subtracting the 1, it might make more sense.

Thanks, Skip!

Dawn

http://www.calliopeconsulting.net

 

[SkipVought]

ii36250 at bellhelicopter dot textron dot com

Post back, please, after you send.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[dawnd3]

Hi Skip, I just emailed you. Thanks!!!!

Dawn

http://www.calliopeconsulting.net

 

[SkipVought]

Dawn,

The problem occurs when the START date is a holiday or weekend date. You have control over the start date.

See what happens when you put the same date in start and end, both holiday and workday with NETWORKDAYS.

Hope this helps.

Skip


Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}