This part focusses on replacing the mathematical functions of the Analysis Toolpak Addin (ATP). Reference to the other parts of this article series:
- Part 1: Mathematical functions
- Part 2: Date/Time functions
- Part 3: Numerical system conversion functions
- Part 4: Others
| 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
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.
Jan Karel
good point. Nice addition.
Frank
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
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.
Hi, do have the replacement code for cumprinc?
NETWORKDAYS FORMULA DOESN’T WORK, EMDASHES FOUL THINGS UP.
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)
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)
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!
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))
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))
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
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