Full Version: Claculating an end date
sssassano
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.
KingMartin
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    LoopEnd 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
sssassano
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.
KingMartin
Oh, sorry, here goes the attachment.

Martin
sssassano
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.
KingMartin
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    LoopEnd 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
KingMartin
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
sssassano
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.
KingMartin
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
KingMartin
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
DateJulijan
2
6/28/200404180
 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.
sssassano
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.
KingMartin
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    LoopEnd Function`
sssassano
in error case the function would simply show #VALUE!, This is what I'am getting. Is there anything else I should try