Jump to content

Date Calculation - Duration


This topic is 7861 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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. wink.gif

-bd

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

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

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

This topic is 7861 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.