i have a spredsheet where i put a date in one cell and i want it to add 5days
..6.7.8.etc up to 15 days but i dont want it to include sundays is this
possible

for example start date 01/07/09 add 15 days not including sundays finish
date will be 17/07/09

I also have one that needs to discount saturday and sunday

Look in HELP for the WORKDAY() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"cufc1210" wrote in message
...
i have a spredsheet where i put a date in one cell and i want it to add
5days
.6.7.8.etc up to 15 days but i dont want it to include sundays is this
possible

for example start date 01/07/09 add 15 days not including sundays finish
date will be 17/07/09

I also have one that needs to discount saturday and sunday

Assuming start date is in A2, number of days to add is in A3:

The WORKDAY function will work for your 2nd crtieria.
=WORKDAY(A2,A3)

For your first scneario, where you want to exclude Sundays, you'll need this
UDF. Open VBE (Alt+F11) then goto Insert - Module, and paste this in.

'===========
Function NoSunday(StartDate As Date, _
AddDays As Integer) As Date
Dim xFinish As Date
Dim xDays As Integer
xFinish = StartDate + AddDays

xDays = 1
Do Until xDays = 0
xDays = 0
For i = StartDate To xFinish
'If a sunday is found, extend finish date
If WorksheetFunction.Weekday(i) = 1 Then
xDays = xDays + 1
End If
Next
StartDate = xFinish
xFinish = xFinish + xDays
Loop

NoSunday = xFinish
End Function
'=================


Close out VBE. Back in your workbook, your formula becomes:
=NoSunday(A2,A3)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cufc1210" wrote:

i have a spredsheet where i put a date in one cell and i want it to add 5days
.6.7.8.etc up to 15 days but i dont want it to include sundays is this
possible

for example start date 01/07/09 add 15 days not including sundays finish
date will be 17/07/09

I also have one that needs to discount saturday and sunday

cufc1210;456656 Wrote:
i have a spredsheet where i put a date in one cell and i want it to add
5days
..6.7.8.etc up to 15 days but i dont want it to include sundays is
this
possible

for example start date 01/07/09 add 15 days not including sundays
finish
date will be 17/07/09

I also have one that needs to discount saturday and sunday

If you add 15 days to 1/7/2009, not including Sundays then I make that
18/7/2009 (unless you are including the start date)

To do that with a formula you can use

=A1-WEEKDAY(A1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1,3))))

where A1 is the start date and B1 the number of days to add, e.g. 15

If you want to include the start date then change references to A1 to
A1-1 i.e.

=A1-1-WEEKDAY(A1-1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1-1,3))))


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126400

thanks guys that a great help

Just one thing barry houdini that formula works great except when i put in a
date for monday

e.g. 3rd Aug 09 the return date is 20th aug 09 when it should be 19th aug 09
every other date from tuesday to saturday works fine any ideas

Cheers

cufc1210

"barry houdini" wrote:


cufc1210;456656 Wrote:
i have a spredsheet where i put a date in one cell and i want it to add
5days
..6.7.8.etc up to 15 days but i dont want it to include sundays is
this
possible

for example start date 01/07/09 add 15 days not including sundays
finish
date will be 17/07/09

I also have one that needs to discount saturday and sunday

If you add 15 days to 1/7/2009, not including Sundays then I make that
18/7/2009 (unless you are including the start date)

To do that with a formula you can use

=A1-WEEKDAY(A1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1,3))))

where A1 is the start date and B1 the number of days to add, e.g. 15

If you want to include the start date then change references to A1 to
A1-1 i.e.

=A1-1-WEEKDAY(A1-1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1-1,3))))


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126400