Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing working days
This post appeared on 7/11/2006.
I repeat it because the author didn't get the answer. I have the same problem. "Excel count weekly off Saturday & Friday. But my case it is Friday & Saturday. I want to define that while using networkdays and workday functions" In my words, if today is Thursday, then WORKDAY (today(),1,0) will give not the next (friday's) date but Sunday's. Please help Greg |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing working days
Not sure if I follow you. If A1 = thursday do you want to drag down a list of dates excluded Fri and Sat. If this is the case then put this formula into cell A2 and drag down =IF(OR(WEEKDAY(A1+1)=6,WEEKDAY(A1+1)=7),A1+3,A1+1) VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=566388 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing working days
If you want to use NETWORKDAYS and WORKDAY functions with Friday and Saturday weekends then use =NETWORKDAYS(A1+1,B1+1) where A1 is your start date and B1 your end date and =WORKDAY(C1+1,D1)-1 where C1 is your start date and D1 the number of workdays you wish to advance. If you also have a holiday range to exclude then you can use these =NETWORKDAYS(A1+1,B1+1,holidays+1) =WORKDAY(C1+1,D1,holidays+1)-1 both of which need to be confirmed with CTRL+SHIFT+ENTER -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=566388 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing working days
It's not so simple.
For example Wednesday+3 will give Monday, but I need Sunday "VBA Noob" wrote: Not sure if I follow you. If A1 = thursday do you want to drag down a list of dates excluded Fri and Sat. If this is the case then put this formula into cell A2 and drag down =IF(OR(WEEKDAY(A1+1)=6,WEEKDAY(A1+1)=7),A1+3,A1+1) VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=566388 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing working days
As the crane-fly said
=WORKDAY(A1+1,3)-1 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Greg" wrote in message ... It's not so simple. For example Wednesday+3 will give Monday, but I need Sunday "VBA Noob" wrote: Not sure if I follow you. If A1 = thursday do you want to drag down a list of dates excluded Fri and Sat. If this is the case then put this formula into cell A2 and drag down =IF(OR(WEEKDAY(A1+1)=6,WEEKDAY(A1+1)=7),A1+3,A1+1) VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=566388 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing working days
Hi Greg,
A1 Your date A2 number of working days to add (Fridays and Saturdays being NO working days) Result: =A1+A2+INT((A2-MOD(A1+A2-6,7)+7)/7)+INT((A2-MOD(A1+A2-7,7)+7)/7)+(MOD(A1+A2+INT((A2-MOD(A1+A2-6,7)+7)/7)+INT((A2-MOD(A1+A2-7,7)+7)/7),7)=6)*2+(MOD(A1+A2+INT((A2-MOD(A1+A2-6,7)+7)/7)+INT((A2-MOD(A1+A2-7,7)+7)/7),7)=0) Please test it. This is just a quick and dirty derivative from http://www.sulprobil.com/html/date_formulas.html HTH, Bernd |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing working days
Hi Bob
Your formula is the best: nice, shortest and correct. Thanks indeed Greg "Bob Phillips" wrote: As the crane-fly said =WORKDAY(A1+1,3)-1 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Greg" wrote in message ... It's not so simple. For example Wednesday+3 will give Monday, but I need Sunday "VBA Noob" wrote: Not sure if I follow you. If A1 = thursday do you want to drag down a list of dates excluded Fri and Sat. If this is the case then put this formula into cell A2 and drag down =IF(OR(WEEKDAY(A1+1)=6,WEEKDAY(A1+1)=7),A1+3,A1+1) VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=566388 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel add working days to a date to result in another date? | Excel Worksheet Functions | |||
Add or Subtract Working Days in Excel | Excel Discussion (Misc queries) | |||
Calendar Days and Option Buttons | Excel Discussion (Misc queries) | |||
Working time and days | Excel Worksheet Functions | |||
Working time and days | Excel Discussion (Misc queries) |