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

  10. Paulo,
    You might try the undocumented DATEDIF function to return the number of months between two dates:
    =DATEDIF(A1,A2,”m”) A1 is the earlier date and A2 is the later one

    If you want to read up on DATEDIF, see Chip Pearson’s discussion at: http://www.cpearson.com/excel/datedif.aspx
    Brad


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.