Stumped: Calculation Field

[bigjames]

I am creating a database to track injured employees time off and have the following fields involved in a calculation:

- Date Last Worked

- Return to Work (Yes/No)

- Date Return to Work

- Time Lost (in days)

The Time lost is simple to calculate if the person has returned to work, but I want it to show the running total if the person has NOT returned to work. I am thinking it should be a CASE or IF calculation, but every way I try this it comes back saying there is a problem with the calculation.

I was thinking that it should be an "if, Return to WorkYN = Yes, then calculate using the Return to Work and Date Last Worked fields and if, Return to work = No, then calculate using "Today" and Date Last worked" but I cannot figure out how to write this out so the program understands.

Any help would be appreciated. It seems like this should be simple and I have done way more complex calculations...not sure why this is stumping me.

[PamRotella]

If the date field were named "Date", I'd use a calc like this:

If( IsEmpty ( Date );Get ( CurrentDate );Date)

If that doesn't work as-is, you might want to make it into a calculation field first. Also, make sure that your calculation field type is set to "Date" -- sometimes it defaults to "number" and messes people up.

[bigjames]

Maybe I was not clear. I am trying to set the "TIME LOST" field as a calculation and it prompts me to Specify Calculation. I want it to output number of says missed.

[comment]

If I understand this correctly, you want something like:

Case ( Date Return to Work , Date Return to Work , Status (CurrentDate) ) - Date Last Worked

Result type is Number, and the calculation must be unstored. I am not sure why you need the Return to Work (Yes/No) field: it seems that the Date Return to Work field already provides the answer to this question.

Edited February 20, 2009 by Guest
fixed a typo

[bigjames]

Ok, this worked partly. I put in exactly what you suggested and now if the "Return to Work" date field is empty, it appears to use the current date to calculate the Time Lost. BUT, when I put a Return to Work Date in it comes back with a value that is very odd.

I put Date Last Worked as Feb 1

I left Return to Work empty

Time Lost showed 19 (days)

I put a Return to Work date of Feb 4

I expected a Time Lost of 3 (days)

The actual result was "733442"

[comment]

You couldn't have used exactly what I suggested, because it had a typo (fixed now). Also, make sure both referenced fields are defined as type Date.

[bigjames]

To clarify, I pasted what you sent, made a couple of changes to the field names so they matched my database and had to add a closed bracket and then it functioned, but it still brings back an odd result when I include a "return to work date".

This is what I have right from my file. The fields involved at "date" fields...

Case( Return to Work , Return to Work , Status(CurrentDate) - Date Last Worked )

Time lost is showing as the 73356 or whatever when I expect to see 3.

[comment]

had to add a closed bracket

That seems to be the issue: I had a missing closing parenthesis - but not at the end. Look at the corrected formula.

[bigjames]

Solved! You got it. Thanks. I have to admit, I do not completely understand that formula, but it works and I am heppy.

Thanks all.

[comment]

It's a rather simple subtraction. The first part is either today or the date of return to work. The second part is the same for both.