calculating number of days

[Steve Wright]

I am stuck.

I have a database that calculates the number of days between two date fields. I need to have a total that omits Saturdays and Sundays.

Any help would be gratefully appreciated.

[-Queue-]

Case( not (IsEmpty(StartDate) or IsEmpty(EndDate));

EndDate - StartDate - 2 * Div( EndDate - StartDate; 7 ) - (WeekOfYear(StartDate) <> WeekOfYear(EndDate) and DayOfWeek(StartDate) > DayOfWeek(EndDate)) * 2 )

[transpower]

Or, alternatively, from the free Calculations and Scripts.fp5 file:

WorkDays (calculation, number result) =

Int((EndDate - StartDate)/7) * 5 + Mod (EndDate - StartDate,7) - If(DayofWeek(EndDate) < DayofWeek(StartDate),2,0)

[Steve Wright]

Thanks to you both for replying to my question. You saved me a lot of time. I ended up doing a sort of mix of both solutions. Plus i tagged in a +1 day to make the calculation inclusive of the two dates. My two variables were date_from and date_to. The result was:

Case( not (IsEmpty(date_from) or IsEmpty(date_to));

Int((date_to - date_from)/7) * 5 + Mod (date_to - date_from;7) - If(DayOfWeek(date_to) < DayOfWeek(date_from);2;0) ) + 1

Cheers

Steve

[QuinTech]

If you need to omit holidays as well, you can try a variation on Mikhail Edoshin's Smart Ranges. May be overkill for your purposes, but it's still an amazing calculation.

Jerry

[Steve Wright]

Thanks for all the helpful hints on this topic. I have started to develop a holidays database. i think it has a few flaws but it might help people get started on a similar system.

Treat it with caution however. The calculations may be a bit flaky.

Basically, there are three main databases recording staff information, annual sick sheets, and annual holiday sheets. The last two record milestones and display a visual summary of each member of staff.

Questions on a postcard to...

holidays.zip