Calculating networkdays (6 day work week)
jvanzomeren wrote on November 03, 2005 15:31 EST |
| Anyone have a tip to calculate network days mon through sat. I have the mail date and received date. |
goober wrote on November 03, 2005 15:56 EST |
=NETWORKDAYS(C12,F12)-(INT((((F12+6)-(C12+6))-MOD(7-(C12+6),7))/7)+1)
Where C12 is start date and F12 is ending date. By changing the +6 in the equation to 1 through 7 you can alter the day off it calculates. Alternately
changing ...F12)-(INT... to ...F12)+(INT... creates a three day weekend.
Good Luck. |
Rao Faizan Ali wrote on December 31, 1969 19:00 EST |
Very Good. The above formula i am looking in these days.
Thanks |
Not working
hemali wrote on December 15, 2005 08:22 EST |
This formula is not working for me.
I entered the date in MM/DD/YYYY format.
Do we have to enter the date in some specific format:confused:
let me know by giving some example that worked for you.
Thanks
hemali |
Not working
hemali wrote on December 31, 1969 19:00 EST |
This formula doesnt work for me..
Do we need to enter date in some specific format..
I am entering it in MM/DD/YYYY format |
JVan
jlvandee wrote on December 31, 1969 19:00 EST |
| Hey, I hate to sound to simple but why not just add 1 to the result of networkdays. |
Not working
hemali wrote on December 31, 1969 19:00 EST |
This formula doesnt work for me..
Do we need to enter date in some specific format..
I am entering it in MM/DD/YYYY format |
RATORRES wrote on December 31, 1969 19:00 EST |
if it doesn't work you have to install and load the analysis toolpack add-in
How?
On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
If necessary, follow the instructions in the setup program. |
wrote on December 31, 1969 19:00 EST |
I have a spreadsheet that I need to calculate when a task is due and use Business Dates.
Here's an example . . . report must be updated within 3 business days of it being received. .
Report due: 12/15/05
Actual Turned in: 12/16/05
3 days to update would be: 12/21/05
How do I set this up in a formula so it excludes Sat/Sun from being added as part of the 3 working days?
Thanks for your help!
|
Adding Business Days
ReneeST wrote on December 16, 2005 13:48 EST |
[B]A[/B]...................................[B]B[/B].................[B]C[/B]..........................[B]D[/B].......................................[B]E[/B]
Task Name...............Duration.........Start Date.........Planned Finish Date............Actual Finish Date
Develop draft SOP.....3 days...........12/12/05..............12/15/05..........................12/19/05
1st draft update.........3 days...........12/20/05.............12/23/05
I have a spreadsheet that I need to calculate dates for when things are next due in a process of setting up a new client.
The date in C2 would be manually entered in, "Start Date"
Formula needed for D2 - to add 3 business days to give the "Planned Finish Date".
The date in E2 would be manually entered in, "Actual Finish Date"
Formula needed for C3, adding next business day to "start the 1st Draft Update"
Formula needed for D3 - to add 3 business days to give the "Planned Finish Date" for the 1st draft.
Anyone able to help with this?
Thanks so much!
Renee
3 business days to 12/16/05 for when
Report due: 12/15/05
Turned in: 12/16/05
3 days to update would be: 12/18/05 But since that is a Sunday, 3 business days would be 12/21.
How do I get Excel to exclude Sat/Sun from being counted as one of the 3 business days?
Thanks for your help! |
Getting #NAME? error
srinivasan wrote on December 31, 1969 19:00 EST |
I am using MS-Excel 2000. This function is not available in the functions list...is it an add-in?.
Pl. revert..
Thanks in advance! |
Reply: hemali
Alan wrote on December 20, 2005 23:37 EST |
Hi hemali,
[QUOTE=hemali]This formula is not working for me.
I entered the date in MM/DD/YYYY format.
Do we have to enter the date in some specific format:confused:
let me know by giving some example that worked for you.
Thanks
hemali[/QUOTE]Anyvalid date should work in the NETWORKDAYS function. Note that the valid date format might be affeted by your provincial settings - such as wanting to use MM/DD/YYYY rather than DD/MM/YYYY (for example).
However, if you enter dates such as 1/1/2004 and 2/2/2005 they would both be valid and equal under either format so you should get a result from the formula.
HTH,
Alan. |
Reply: ReneeST
Alan wrote on December 20, 2005 23:55 EST |
Hi ReneeST,
[QUOTE=ReneeST][B]A[/B]...................................[B]B[/B].................[B]C[/B]..........................[B]D[/B].......................................[B]E[/B]
Task Name...............Duration.........Start Date.........Planned Finish Date............Actual Finish Date
Develop draft SOP.....3 days...........12/12/05..............12/15/05..........................12/19/05
1st draft update.........3 days...........12/20/05.............12/23/05
I have a spreadsheet that I need to calculate dates for when things are next due in a process of setting up a new client.
The date in C2 would be manually entered in, "Start Date"
Formula needed for D2 - to add 3 business days to give the "Planned Finish Date".
The date in E2 would be manually entered in, "Actual Finish Date"
Formula needed for C3, adding next business day to "start the 1st Draft Update"
Formula needed for D3 - to add 3 business days to give the "Planned Finish Date" for the 1st draft.
Anyone able to help with this?
Thanks so much!
Renee
3 business days to 12/16/05 for when
Report due: 12/15/05
Turned in: 12/16/05
3 days to update would be: 12/18/05 But since that is a Sunday, 3 business days would be 12/21.
How do I get Excel to exclude Sat/Sun from being counted as one of the 3 business days?
Thanks for your help![/QUOTE]You could use the WORKDAY function that is included as a UDF in the analysis toolpak add-in to do that:
=WORKDAY(start_date,days,holidays)
However, be aware that if you use a function outside of the core excel functions, then anyone trying to use your workbook that doesn't have the add-in too will get error messages.
This is an alternative to the WORKDAY function using only core excel functions (same format of arguments as above):
=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)))
Obviously that is a lot more complicated, but it will always work (I think!)
Alan. |
modified
baghersad wrote on December 31, 1969 19:00 EST |
I have a database on excel. it has 4 worksheets.
I enter data in the worksheets every day. I entered information in the database of 13th until 17th sep 2004
The information is wrong.
how could I seprate to delete the information onto this database. please?
Email: baghersad@yahoo.com
thanks
Baghrsad
|
Days Formula
mabvuto wrote on December 31, 1969 19:00 EST |
Who can help me with a formula that should be doing the following:
It should be deplaying dates of weekly days in Monday, Wednesday and Friday excluding sundays. Or Tuesday, Thursday Saturday excluding Sunday. e.g
Mondays, Wednesdays and Fridays of June
June 1, 2006
June 3, 2006
June 6, 2006
Tuesdays, Thursdays and Saturdays of June
June 2, 2006
June 5, 2006
June 7, 2006
This should be happening after entering any date on the first cell of the List and should accommodate up to 3 months
Help!!
Regards
Mabvuto |
The Total Money Makeover. : A Proven Plan for Financial Fitness
