« Replacing the Analysis Toolpak Addin - Part 1
Replacing the Analysis Toolpak Addin - Part 3 »

Replacing the Analysis Toolpak Addin - Part 2

19th December 2004, 04:02 am by Frank Kabel

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

Category: Worksheet Functions, Date Functions  |  Comment (RSS)  |  Trackback

8 Comments

  1. jkpieterse:

    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.

    20 December 2004, 7:35 am

  2. Frank Kabel:

    Jan Karel
    good point. Nice addition.
    Frank

    20 December 2004, 7:41 am

  3. aroberts:

    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

    23 March 2005, 2:55 am

  4. Keith:

    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.

    25 August 2005, 8:49 pm

  5. Nneka Ene:

    Hi, do have the replacement code for cumprinc?

    16 January 2006, 10:23 am

  6. PAUL:

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

    7 February 2006, 10:18 am

  7. Mike Holland:

    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)

    3 May 2006, 1:22 pm

  8. S Gray:

    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)

    23 July 2006, 3:24 am

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment