adding days from one cell with a date in it to another cell

[cufc1210]

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

 

[Niek Otten]

Look in HELP for the WORKDAY() function

 

[Luke M]

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)

 

[barry houdini]

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

for example start date 01/07/09 add 15 days not including sunday
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 tha
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 t
A1-1 i.e.

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

 

[cufc1210]

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