Date Calc Question 1

[tcoyle777]

Is there away to get the dates in between a Date Start and a Date End fields?

 

[TStriker]

Can you expand on your question a bit?

For example:

End = Nov 10, 2007
Start = Nov 5, 2007

Do you want the result to be:

Nov 6, 2007
Nov 7, 2007
Nov 8, 2007
Nov 9, 2007

Or do you want it to be:

5 (because 10 - 5 = 5)

-Striker

 

[tcoyle777]

TStriker,

thanks for responding.
Yes I want the actual dates not just the number of days between the start and end dates. I need to get the dates to check for conflicts in double booking rooms and equipment.

Tim

 

[JeanW]

You could make a recursive custom function for it.

This way you can even restrict the range to given weekdays.

 

[tcoyle777]

I have never set up a custom function, could you give me a example of how I might set one up with the example of needing the date ranges described above?

 

[JeanW]

Before I go in detail, do you have the Advanced version of FM.

This is the only version where you actually can make custom functions.

 

[tcoyle777]

Yes, Filemaker 9 Advanced

 

[JeanW]

I don’t know your structure so I go very wide.
Drop what you can’t use or take several items together.

Anyway, try this first in a file you make just to test and understand this technique before you use it in your application.

Make a valuelist with all the days of the week going from 1 to 7 and put this as checkbox on a layout in a field you create for that (weekDays), where 1 is for Sunday and 7 for Saturday and the rest for the days in between.
Make fields:
startDate – date
endDate - date

Go to create Custom Function (I don’t have FM 9, so I don’t know yet if they changed the access way – in FM 8.5 it is File -> Define -> Custom Function)

Create a new CF, name it ‘WeekDayRange’ with parameters:
startDate
endDate
weekdays

make the calculation:

Case (
startDate and not IsEmpty ( FilterValues ( DayOfWeek ( startDate ) ; weekDays ) ) ;
startDate & ¶
)
&
Case (
startDate < endDate ;
WeekDaysRange ( startDate + 1 ; endDate ; weekDays )
)

Create an other calc field daysInRange, result text:
WeekDayRange (startDate ; endDate ; weekDays )and put this on your layout. Here you will see the result.

Take the 'WeekdayRange' from the list of function in the calculationbox.

In Browse Mode:

Fill in a date in startDate and endDate and check all the days of the week in your checkboxfield if you want all the weekdays between the two given days.
If you want only from Monday to Friday, uncheck Saturday and Sunday etc.

What it does.
Gives a ¶ separated list of the dates between the initial dates for the given days in the weekdays field.
This field is a ¶ separated numberlist of chosen days because it's a checkbox field.
The list will be sorted by date.

This field you can use in a relationship or you can parse out each date in separate field if you need.

HTH

 

[tcoyle777]

This is great. Is there away that I can set it up so that I don't have a check box field, but have that field already have 1
2
3
4
5
6
7
in it, so the user won't have to check them.

but it solved my problem pulling duplicate bookings for me.

Thanks so much for all your help with all my questions. My first massive database is almost done!

 

[JeanW]

Yes, make it auto-enter 1 and put the value list setup over it.

You will keep the flexibilty of adding more days if you ever need it.

 

[JeanW]

Woops, sorry, thought you only wanted a 1 in the field.

Still, make it auto-enter but with data:
1
2
3
4
5
6
7

Now you have the range as ¶ list.

 

[tcoyle777]

perfect, you rock