Please suggest a formula

Excel 1103 views 5 replies

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.

Replies (5)

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.

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

Dear Mr. Ashish & Rajneesh

 

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

 

Regards

 

VENUGOPAL

see in formula tab then in date and time functions.

 

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)

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  2. Click Add-Ins, and then in the Manage box, select Excel Add-ins.
  3. Click Go.
  4. 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.
  5. 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.


CCI Pro

Leave a Reply

Your are not logged in . Please login to post replies

Click here to Login / Register