Date Calculation - Duration

[jnmorrison]

I work with schedules a bunch. I have "Date_Due" & "Time_Due" fields as well as "End_Date" & "End_Time" fields. I would like to create a calculation that compares the end date & time with the due date & time and returns one of three responses: On Shedule, Completed On Time, Completed Late.

I know there are plug ins that help with this, but I'd like to do this without them, if possible.

[LiveOak]

Status (calculation, text) =

Case(

End_Date < Due_Date, "Completed On Time",

End_Date = Due_Date and End_Time <= Time_Due, "Completed On Time",

End_Date > Due_Date, "Completed Late",

End_Date = Due_Date and End_Time > Time_Due, "Completed Late",

"Not enough information to tell if a job is on schedule"

)

A little FM humor. If a job has not ended and the due date is still in future, more information is need to tell if a job is on schedule. Just because your homework isn't due for another 2 minutes doesn't mean you a on schedule if you haven't started it.

-bd

[jnmorrison]

Thanks a bunch...it works great. Another quick question about the Case Statement... If I create a case statement where more than one of the items in the statement are true, how does it determine which one to honor? Does it go from right to left or vice versa and stop at the first item that is true?

[LiveOak]

Case statements evaluate in order. The first match you come to wins. -bd

[jnmorrison]

In order from left to right?

[jnmorrison]

I think i might not be explaining myself well. Here's an example:

Let's say my production process has four stages... plan, build, QC & Ship

I have a status field which I want to tell me what stage I'm in based on which items have been checked off. If might read something like this:

case(plan = Yes, "Build",build = Yes, "QC",QC = Yes, "Ship", "Plan")

In this example, several items can be true. Does it read from left to right, check the first test, see that it is true and stop, or does it keep going until it does not find a test that is true?

If you try this test and give plan, build & QC the value of "Yes", the result of the calculation is "Build". This implies it stopped as soon as it found a statement which is true, from left to right. To make this work, and correct me if I'm wrong, I would need to reverse the order of tests.

Any feedback?

[LiveOak]

Boy are we making this hard! The tests are performed in the order you read the statement in English, left to right and top to bottom.

Case(

Test 1, Result 1,

Test 2, Result 2,

Test 3, Result 3,

Result 4)

If Test 1 succeeds, Result 1 is returned. If Test 1 fails and Test 2 succeeds, Result 2 is returned. If Test 1 fails and Test 2 fails and Test 3 succeeds, Result 3 is returned. If Test 1 fails and Test 2 fails and Test 3 fails, Result 4 is returned. If Tests 1 succeeds and Test 2 succeeds and Test 3 succeeds, Result 1 is Returned. If Test 1 fails and Test 2 succeeds and Test 3 succeeds, Result 2 is returned.

-bd

[Kurt Knippel]

quote:

---

Originally posted by jnmorrison:

In order from left to right?

---

All CASE & IF statements evaluate to a single boolean value of 1 or 0. As soon as the CASE hits a 1 it stops.

There is no right to left, it goes statement by statement (or test by test).

For instance: "End_Date = Due_Date and End_Time > Time_Due" is a single statement (or test) and will either evaluate to 1 or 0.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

[TC]

In theory you don't need to have all the previous fields checked to determine what state a product is in, although maybe your company works differently. Eg/ if something is still in the planning stage no other boxes need to be checked. However if you're then in the building stage there's no reason to have the plan option checked as you can't be in the building stage without the plan already done -- forgive me if I'm missing out on something here. Then it's a simple task of using an IF or CASE statement ( I prefer to use CASE ) to determine which field or option is currently checked.

--TC