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

C

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
 
L

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)
 
B

barry houdini

cufc1210;456656 said:
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)))
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top