MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dates


Posted by Sam on October 17, 2001 3:47 AM

I have 2 questions.

1: Is there a way in excel that I can get a cell to subtract a date in a cell from the system date, so that i can keep track of how long an issue has been open?

2: If the first question is possible, is there a way to exclude weekend days from that calculation?


Posted by Dan on October 17, 2001 5:02 AM

If you are using Excel 97 (and later versions?) you will need to install the Analysis Tools Add-ins to make this work. Go to Tools -> Add-ins and make sure the Analysis Toolpak is check-marked. Once you do that you can use the NETWORKDAYS worksheet function to do what you need. From the Excel Help Section:

Syntax:
NETWORKDAYS(start_date,end_date,holidays)

Start_date is a date that represents the start date.

End_date is a date that represents the end date.

Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays.

Remark

If any argument is not a valid date, NETWORKDAYS returns the #NUM! error value.

Example

NETWORKDAYS(DATEVALUE("10/01/91"), DATEVALUE("12/01/91"), DATEVALUE("11/28/91")) equals 43.


HTH