Go AD-Free with CAclubindia Pro and Daily WhatsApp Updates. Subscribe Now!

Please suggest a formula

Pranjal (Student CA IPC / IPCC) (531 Points)

24 February 2012

I've to request for funds for any activity from Regional Office at least 21 days prior to the actual date of activity.Please suggest me an excel formula where by when I put the activity date it automatically gives me the date by which I've to request funds.Please remember we have a five day working week & as such Saturdays & Sundays are not taken into account while calculating the said 21 days.

Reply
Follow
Share

5 Replies

ashish gupta ( student) (1922 Points)
Replied 25 February 2012

First apply the networkdays formula.

that is networking days (excluding Saturday and Sunday) between two different dates i.e. the date of activity and some earlier date. Formula is =networkdays(firstdate, second date). Then Apply the Goal Seek to set the value of networkdays to 21 by changing the first date cell.

Upgrade to CAclubindia Pro. Subscribe Now!

Rajneesh Kumar (Finance & Accounts) (29 Points)
Replied 25 February 2012

Hi Use Below Formula in Excel

In 2nd Column Give Desire date

In 3rd Column Give -21 Days

In 4th Column Give =WORKDAY(B2,C2-1) where B is 2nd Column & C is 3rd Column

Regds

Rajneesh

Job Stage	Start Date	Days Required	End Date
Survey	Fri 24-Feb-12	21	Fri 23-Mar-12

VENUGOPALAKRISHNAN P K (ACCOUNTS OFFICER) (33 Points)
Replied 25 February 2012

Dear Mr. Ashish & Rajneesh

I could not get this correctly. Could you please explain again. Which EXCEL Version have this

Regards

VENUGOPAL

ashish gupta ( student) (1922 Points)
Replied 25 February 2012

see in formula tab then in date and time functions.

Kamaraj Mani. N (Chartered Accountant - Product Manager) (553 Points)
Replied 26 February 2012

To use NETWORKDAYS function, you need to install Analysis ToolPak Addin

Installation steps:

(for MS-Excel 2003)

1. Click "Tools" - "Add-Ins".
2. Check "Analysis ToolPak".
3. Click "OK".
4. Follow the on-screen prompts or instructions.

(For MS-Excel 2010)

Click the Microsoft Office Button , and then click Excel Options.
Click Add-Ins, and then in the Manage box, select Excel Add-ins.
Click Go.
In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK.
1. Tip If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.
2. If you get prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.
After you load the Analysis ToolPak, the Data Analysis command is available in the Analysis group on the Data tab.

Note To include Visual Basic for Application (VBA) functions for the Analysis ToolPak, you load the Analysis ToolPak - VBA add-in the same way that you load the Analysis ToolPak. In the Add-ins available box, select the Analysis ToolPak - VBA check box, and then click OK.