I need to calculate an end date based off of:

30 days on mondays,wednesday fridays and saturdays.

is it possible to have (seven) check box ( of all 7 days ) to select which days the 30 will be done on.

and is it possible to have the 30 days be highlighted on a calendar. kind of like a part-time employee, but that the employee will only work 30 days.

Hello,

formula solution could be quite complicated = I have no idea how to do it

So I created a custom UDF,

Function EDate(StartDate As Date, NrDays As Long) As Date
    Dim Cnt As Long
    Application.Volatile
    EDate = StartDate
    Do Until Cnt = NrDays
        ' for different range, you might want to adjust [B3:B9] and +2,4
        If Cells(Application.Match(Format(EDate + 1, "dddd"), [b3:b9], 0) + 2, 4) Then _
            Cnt = Cnt + 1
        EDate = EDate + 1
    Loop
End Function

use like this:

[color=white]Microsoft Excel - DateUDF.xls ___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
F13F14F15 =
	A	B	C	D	E	F
1
2
3		Sunday		TRUE
4		Monday		TRUE
5		Tuesday		FALSE
6		Wednesday		TRUE
7		Thursday		FALSE
8		Friday		FALSE
9		Saturday		TRUE
10
11
12		Start		Nr of Days		End
13		6/1/2004		4		6/7/2004
14		6/5/2004		30		7/26/2004
15		6/2/2004		20		7/7/2004
Sheet1

[color=#339966 size=1][HtmlMaker 2.42] [color=#339966 size=1]To see the formula in the cells just click on the cells hyperlink or click the Name box
[color=red size=1]PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

You can't see the checkboxes here, so I enclose also an attachment.

I do not quite understand what do you mean by highlighting in a calendar. Where is the calendar placed and in which format?

Martin

King Mrtin thanks again for your help, but I didn't get the attatchment. and I'am not really understanding how your getting this to work.

Oh, sorry, here goes the attachment.

Martin

King Martin, Again I'am sorry although I did get your attachment. I can't get it to work. I get a syntax error in the;
Function EDate(StartDate As Date, NrDays As Long) As Date Dim Cnt As Long Application.Volatile EDate = StartDate Do Until Cnt = NrDays ' for different range, you might want to adjust [B3:B9] and +2,4 If Cells(Application.Match (EDate + 1, "dddd"), [b3:b9], 0) + 2, 4) Then _ Cnt = Cnt + 1 EDate = EDate + 1 LoopEnd Function

also, is gives me a pop up about not finding the project or library.
I'am stumped.
If you got a chance to look at the attachment I sent, you'll see that your right on track with what i'am trying to accomplish.

Also there is a Date Calculator by leithauseresearch.com that we currently use it's exactly what I need to create on my worksheets. is that possissble
again thank you, thank you, thank you.

Edited by: sssassano on 06.25.04.

Hello again,
I tried to adjust your attachment but it won't allow me to insert checkboxes (probably due to the dll error when trying to load the calendar ActiveX control).

Try to change the name to EndDate (EDate exists already in Analysis toolpack, I forgot about it):

Function EndDate(StartDate As Date, NrDays As Long) As Date
    Dim Cnt As Long
    Application.Volatile
    EndDate = StartDate
    Do Until Cnt = NrDays
        ' for different range, you might want to adjust [B3:B9] and +2,4
        If Cells(Application.Match(Format(EndDate + 1, "dddd"), [b3:b9], 0) + 2, 4) Then _
            Cnt = Cnt + 1
        EndDate = EndDate + 1
    Loop
End Function

Paste in a normal module and use as in my attachment.

I'd like to help you more but I would need a simplified sample from you - without the date calculator, pure Excel file.

regards,

Martin

Hello,
try out the attached workbook. It might be close to what you need. I work with Data validation for Sentence drop-down, conditional format for shading and User-defined function (UDF) for EndDate.

This function is still not perfect for all possible combinations - is the starting date always the first day of a custody or may the first day served follow after the start date?

Martin

I'am loving it, but still having the same problem with the library or something, this is what I was going to send you and as you can see you were thinking just like me ( except for alot smarter ). I tried the end date instead of the edate but still not working any other ideas.

Hello,
I have re-downloaded my attachment and it works flawlessly. Also, it's pretty close to what you have in your file.
When do you get the error?

1) Which version of Excel do you use? I have XL2000.

2) Could somebody else please download my last attachment and see if it behaves normally? Thank you.

Martin

BTW, regarding Julijan date as requested in your attachment:

[color=white]Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A2B2 =
	A	B	C	D
1	Date	Julijan
2	6/28/2004	04180
Sheet1

[color=#339966 size=1][HtmlMaker 2.42] [color=#339966 size=1]To see the formula in the cells just click on the cells hyperlink or click the Name box
[color=red size=1]PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The error I'am getting now is with [SUMPRODUCT(--(d3:d9))] the other error was with If Cells(Application.Match(Format(EndDate + 1, "dddd"), [b3:b9], 0) + 2, 4) Then _

I'll see if someone else could download, and I'am using office 2000, is there any add-ins or libraries that I need to find .

Thanks again for help, you have been more than gracious.

Do you get an error in my file? In the file you downloaded here ?

How's that possible? Also, one of the guys around here (Stu) has dowloaded the file and it worked also flawlessly for him.

There should be no error, in error case the function would simply show #VALUE!

I added some comments into the UDF to make things more clear:

Function EndDate(StartDate As Date, NrDays As Long)
    Dim Cnt As Long
    'the following line expects that D3:D9 are linked to 7 checkboxes
    'it checks if at least one day is TRUE; if not it returns an error value
    If [SUMPRODUCT(--(D3:D9))] = 0 Then EndDate = CVErr(xlErrValue): Exit Function
    Application.Volatile
    EndDate = StartDate
    Do Until Cnt = NrDays
        ' the following line expects that weekdays in dddd format (Sunday, etc....)
        ' are housed in B3:B9
        ' for different range, you might want to adjust [B3:B9] and +2,4
        If Cells(Application.Match(Format(EndDate + 1, "dddd"), [b3:b9], 0) + 2, 4) Then _
            Cnt = Cnt + 1
        EndDate = EndDate + 1
    Loop
End Function

in error case the function would simply show #VALUE!, This is what I'am getting. Is there anything else I should try