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. 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

  3. 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.

  4. 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)

  5. 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)

  6. 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!

  7. 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))

  8. 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))

  9. 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

Leave a Reply

Your email address will not be published. Required fields are marked *