Well for one thing, you haven't described the solution that you tried to use. There's more than one possible method you might set up for this.
but when you have multiple fields that you need to determine the number of days between the two dates my solution fails???
What do you mean by "multiple fields" why would you need more than a start and end date?
thanks philmodjunk for your reply: here is the solution I was trying to employee:
Define a calculation field (WorkDays) with a number result with this formula:
5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )
If a holiday (of one or more days) occurs between StartDate and EndDate, and you don't want to count the holiday as a work day, then some changes are required. First, create a new Table, HOLIDAYS, which contains the following fields:
Holiday (Text) - Optional
By multiple fields I mean I have a number of different variables each with a potentially different start date. my ultimate goal is to calculate the number of days and the determine cost
By multiple fields I mean I have a number of different variables each with a potentially different start date.
Yes but each of those should use the same two fields, but in different records. That way the same set up calculates each number of work days and you have several options for calculating a total from a group of such records.
Here in one my working systems we use a slightly less convoluted approach. I set up a table with one record for every day of the year and mark all the days that the business is closed--whether due to a holiday or just because it is Sunday. I'm able to designate certain days of the week as "always closed" and I can click controls or enter a date into a special date field to add additional dates closed for holiday purposes.
I then use a relationship that only matches to records for dates that the business is open and count the total number of records.