Date Calculation

[Baylah]

Can someone help me with a date calcualtion?

I am trying to figure out how to get a result for "Number of days an order is in house" but I want the calculation to factor out weekends so if the order comes in on Friday and goes out on Monday it does not show in house of 3 days.

All I currenty have is: Date_Out - Date_in = Number of days in house. Obvioulsy this won;t work and if I enter an IN_date and no out date I get a return of -732067 until I enter the Out date. There has to be a better way!

Any help would be greatly appreciated.

Thanks,

Steve

[comment]

See if this recent thread helps.

To prevent the calculation returning a result when one of the fields is empty, wrap it in a Case() function, e.g.

Case ( DateIn and DateOut ;

<< calculation here >>

)

[Baylah]

Thanks, I already figured that part out, I am now continuing to try and figure out the "leave out the weekend days" portion but still no luck.

I have always wondered why FileMaker doesn;t include a "check box" or some other method that states "if one or more fields in caclulation is empty do not evaluate."? I'm sure there is a god reason though.

Steve

Edited May 5, 2006 by Guest

[comment]

I thought the thread I pointed to deals precisely with the "leave out the weekend days" portion...

[Lee Smith]

You did.

[Baylah]

I didn;t see that part, I'm sorry. Duh

Thanks,

Steve

[Baylah]

This works for me:

Div((Date_Out-Date_In); 7) * 5 +

Mod(Date_Out-Date_In; 7 )-

Case( DayOfWeek(Date_Out) < DayOfWeek(Date_In);2;0)

But now I can't figure out how to get the "case" statement to work properly because if I don't have my "date_out" field populated (and this is a field I fill out "after the fact") I get a return of -xxxxxx (number depends on date_in.)

I did have that portion of it working properly until I added/changed to the above.

Fields =

date_in: date field

date_out: date field

days_in_house: Number: auto-enter Calculation replaces existing value.

Anybody able to offer any help?

thanks,

Steve

[Baylah]

This Seems to work, Thanks for the help getting me here.

If (IsEmpty(Date_Out) ; " " ; Div((Date_Out-Date_In); 7) * 5 + Mod(Date_Out-Date_In; 7)- Case( DayOfWeek(Date_Out) < DayOfWeek(Date_In);2;0) )

I'm so happy!

Steve