Compare two dates and calculate the days between them. 2

[Tompski]

Hello!

I have what I think is a tricky question. I'm working on a database for my company and need to figure out how to compare two dates (sign date & start date) and get the number of days between them or the number of month between them. I hope anyone have a clue what to do. I'm curently working in FMPro5

 

[JeanW]

Calculate weekdays between two dates (substitute start and stop by your datefields)

(Int((stop-6)/7)-Int((start-6)/7))*5+
If(Mod(stop-6;7)-2>0;Mod(stop-6;7)-2;0)-
If(Mod(start-6;7)-2>0;Mod(start-6;7)-2;0)


Calculate months between two dates

12-Month(start)+Month(stop)+(12*(Year(stop)-Year(start)-1))


Int((EndDate - StartDate)/7) * 5 + If(DayofWeek(EndDate) < DayofWeek(StartDate),
Min(5, DayofWeek(EndDate) - 1) + Max(0, 6-DayofWeek(StartDate)),
If(DayofWeek(StartDate) < 7, Min(6,DayofWeek(EndDate)) - DayofWeek(StartDate), 0))

An other approach is:
Case( not( IsEmpty(Start) or IsEmpty(Stop) ) ,
5 * Int( (Stop - Start)/7 ) +
Middle( "0123455123455512344451233345122234511123450012345" ,
7 * ( DayofWeek(Start) - 1 ) + Mod( Stop-Start , 7 ) + 1 , 1 )
)


Question is if you want ALL the days, or only working days (no weekend or holidays in between)
Only working days (but still possible holidays):
Int((Stop - Start)/7) * 5 + If(DayofWeek(Stop) < DayofWeek(Start),
Min(5, DayofWeek(Stop) - 1) + Max(0, 6-DayofWeek(Start)),
If(DayofWeek(Start) < 7, Min(6,DayofWeek(Stop)) - DayofWeek(Start), 0))

If you want no holidays you need a second file with the dates and a lookup.
If you need that, feel free to ask.

HTH
JW