# Replacing the Analysis Toolpak Addin – Part 2

This part focusses on replacing the mathematical functions of the Analysis Toolpak Addin (ATP). Reference to the other parts of this article series:

Date ATP functions
ATP Function Description ATP Syntax Replacement Formula Array formula
EDATE Returns the Excel date / time serial number of the date that is the indicated number of months before or after the specified number of months from the start_date =EDATE(start_date,months) =DATE(YEAR(start_date),MONTH(start_date)+months,MIN(DAY(start_date),DAY(DATE(YEAR(start_date),MONTH(start_date)+months+1,0)))) No
EOMONTH Returns the Excel date / time serial number of the last day of the month before or after a specified number of months from start_date =EOMONTH(start_date,months) =DATE(YEAR(start_date),MONTH(start_date)+months+1,0) No
NETWORKDAYS Returns the number of whole working days between two dates excluding specified holidays =NETWORKDAYS(start_date,end_date,holidays) =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(start_date&”:”&end_date)))<>1),((WEEKDAY(ROW(INDIRECT(start_date&”:”&end_date)))<>7)),(COUNTIF(holidays,ROW(INDIRECT(start_date&”:”&end_date)))=0))*(1-2*(start_date>end_date)) No
WEEKNUM Returns the weeknumber in the year. The 1st week starts Jan-1; the 2nd week starts the following Sunday (return_type = 1) or Monday (return_type = 2).
Replacement formula returns the ISO weeknumber.
=WEEKNUM(serial_num,return_type) =1+INT((serial_num-DATE(YEAR(serial_num+4-WEEKDAY(serial_num+6)),1,5)+
WEEKDAY(DATE(YEAR(serial_num+4-WEEKDAY(serial_num+6)),1,3)))/7)
No
WORKDAY Returns the Excel date / time serial number of the date before or after a specified number of workdays excluding holidays =WORKDAY(start_date,days,holidays) =start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(ROW(INDIRECT(“1:”&ABS(days)*10))),2)<6)*ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT(“1:”&ABS(days)*10))),holidays,0)),ROW(INDIRECT(“1:”&ABS(days)*10))),ABS(days))) Yes

Only missing function if this area is YEARFRAC as I consider this function ‘buggy’ anyway. For more about this read this newsgroup post of Norman Harker.

Frank

## 14 thoughts on “Replacing the Analysis Toolpak Addin – Part 2”

1. Might be nice to show a reverse formula, I have one for extracting the date of the monday in a given weeknumber (ISO):

=DATE(\$E\$1,1,1)+(E2-IF(WEEKDAY(DATE(\$E\$1,1,1),2)<5,1,0))*7-WEEKDAY(DATE(\$E\$1,1,1),2)+1

Cell E1 contains the year and E2 the weeknumber.

2. Frank Kabel says:

Jan Karel
Frank

3. aroberts says:

I would like some help with a formula please.

I have a column with several hundred dates in it in the format of dd/mm/yyyy.

I need to count the number of times each date appears for the year only.

eg, I need a total for 1998, 1999, 2000 etc.
Note I dont want a single formula to do all dates.

The results are to separate in each.

I hope this explins.

thanks, Alan

4. Keith says:

I am having an issue trying to come up with a formula to help me make a forcasting excel sheet. All I need to do is be able count how many tuesday there are between two dates. I have tried everything that I can think of.

5. Nneka Ene says:

Hi, do have the replacement code for cumprinc?

6. PAUL says:

NETWORKDAYS FORMULA DOESN’T WORK, EMDASHES FOUL THINGS UP.

7. Mike Holland says:

I note that you have a replacement for ISO.WEEKNUM nut don’t show WEEKNUM. Here’s a replacement for =WEEKNUM(serial_num,return_type)

=INT((WEEKDAY(DATE(YEAR(serial_num),1,2-return_type))+serial_num-DATE(YEAR(serial_num),1,-5))/7)

8. S Gray says:

Hi,

I’m trying to use your formula for workday (without the analysis pak) in VBA, how do i go about this. The formula i’m using is;

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(ROW(INDIRECT(“1:”&ABS(days)*10))),2)

9. Kamesh says:

Hi,
I need help with excel formula to get number of days in a given month before a specified date and after a specified date. Is there a single formula for this?
Thanks!

10. Victor says:

A slight improvement on the NETWORKDAYS replacement. If you use WEEKDAY(date,2), you can remove one of the sumproduct terms:

SUMPRODUCT((WEEKDAY(ROW(INDIRECT(start_date&”:”&end_date)),2)end_date))

11. Victor says:

A slight improvement on the NETWORKDAYS replacement. If you use WEEKDAY(date,2), you can remove one of the sumproduct terms:

SUMPRODUCT((WEEKDAY(ROW(INDIRECT(start_date&”:”&end_date)),2)<6),(COUNTIF(holidays,ROW(INDIRECT(start_date&”:”&end_date)))=0))*(1-2*(start_date>end_date))

12. Paulo Nascimento says:

Hi,

Concerning the EDATE(), how can we do something the way around? I mean get the number of months between two dates?

Help would be appreciated.
BR,
Paulo