date calculator

[C.S.]

Hi,

I'm building a calculator of sorts for dates. I have a start date in A1. Next to the start date I have a list:

10 days from -start date- is:
15 days from -start date- is:
20 days from -start date- is:
and so on...

I need a formula to return a date that is however many days specified from the start date. If that date lands on a sat or sun I need it to return me the date for the monday after. For example, if my start date is 10/15/09 (thursday) and 10 days from that is 10/25/09 (sunday) I need it to return the date of 10/26/09 (monday). I also need it exclude a range of holidays i have listed.

Thanks,
c.s.

 

[taigovinda]

Here's a nice solution for you: http://www.mrexcel.com/forum/showthread.php?t=79653

 

[C.S.]

thanks for the suggestion you linked except that one excludes saturdays and sundays, i need it to include them in the count, but, if the end date lands on a saturday or sunday, then I need it to give me the date of the monday after that weekend.

cs

 

[taigovinda]

Oh yeah, I guess it's not the same... How about this, does it return the expected results?

Excel Workbook
	A	B	C	D
1	Start	Days	End	Holidays
2	10/22/2009	10	11/3/2009	2-Nov
3	10/22/2009	15	11/6/2009
4	10/22/2009	20	11/11/2009
1

Excel 2000

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
...D2:D10 is the holidays list.
[/B]

 

[C.S.]

Works great! One more thing, how would the formula need to be adjusted if I wanted the date to move to the friday before as opposed to the monday after (provided the end date lands on a sat or sun)?

Thanks, you've been a tremendous help!

cs

 

[C.S.]

I've noticed something else I hope you can help with. It seems as if the formula will take into account for the holidays by not allowing a date to land on it, however, it doesn't seem to exclude them from the day count. For instance, if my start date is 1/12/09 and there is 10 days I want it to calculate out to, it should return me a date of 1/23/09 due to the 19th being a holiday. As it is, it is giving me a date of 1/22/09. Thanks again for all your help, I hope this makes sense.

cs

 

[Sandeep Warrier]

I think the following UDF would meet your requirements...

Excel Workbook
	A	B	C	D
1	Start Date	Days to Add	Holiday Range	End Date
2	12/01/2009	10	19/01/2009	25/01/2009
3			20/01/2009
4			21/01/2009
???

Excel 2003

Cell Formulas
Range	Formula
D2	=GetDate(A2,B2,$C$2:$C$4)

Function GetDate(StartDate As Date, AddDays As Long, Optional HolidayRange As Range) As Date
    Dim Rng As Range
    Dim Count As Long
    Dim NewDate As Date
    Application.Volatile
    NewDate = StartDate + AddDays
    Select Case Weekday(NewDate)
    Case 7
        NewDate = NewDate + 2
    Case 1
        NewDate = NewDate + 1
    End Select
    If Not HolidayRange Is Nothing Then
        For Each Rng In HolidayRange.Cells
            If (CLng(Rng.Value)<= CLng(NewDate)) And (CLng(Rng.Value) >= CLng(StartDate)) Then
                NewDate = NewDate + 1
            End If
        Next Rng
    End If
    GetDate = NewDate
End Function

 

[C.S.]

sandeep,

thanks so much for your response, except the problem is I have no idea how to use your suggestions. I apologize for being inept, but could you give me a little step by step process outline? I have no experience whatsoever with code or macros.

THanks again,
cs

 

[Sandeep Warrier]

sandeep,

thanks so much for your response, except the problem is I have no idea how to use your suggestions. I apologize for being inept, but could you give me a little step by step process outline? I have no experience whatsoever with code or macros.

THanks again,
cs

You will need to paste the code in the Visual Basic Editor (VBE).

In your Excel window press Alt+F11 for the VBE to open up. Then, on the menu bar at the top, go to Insert | Module. Paste the code from my earlier post in the white area that appears on the right.

Close the VBE.

You can use this function as a normal worksheet function. It has the format...

=GetDate(StartDate, AddDays, HolidayRange)

StartDate accepts a date as an input, AddDays accepts an integer as input, HolidayRange is a range that contains your holiday dates. HolidayRange is optional i.e. the code will work even without input for this field.

In the example in my earlier post, the StartDate is in A2, B2 contains the number of days to be added and C2:C4 contains the HolidayRange.

Also, you can remove the line: Dim Count as Long from the code as it is not required.

Hope this helps.

 

[C.S.]

sandeep,

thanks for the directions it was a piece of cake, except I need the function to adjust the target date, if it lands on a sat or sun, to the friday before the weekend, instead of after.

thanks,
cs