Days Overdue

[skynight]

I tried but failed to make a field that will display the number of days an action, when carried out, is overdue. I provided a field to record the due date, and another to record the date the action was carried out. I made a calculation field "date 1 - date 2" and it shows the number of days between the two.

However what I want it to show is the number of days between present date and due date (if possible, showing black when there is time to go and red when overdue) and will freeze at the present (overdue) reading when a date of completion is inserted.

I would also like it to email a message every day that the action is overdue, but that might be pushing it.

Any help, with details because I am a neophyte here, would be appreciated.

[LaRetta]

Hi Skynight, you might try:

If (

DateOfAction ; DateOfAction - DueDate ;

TextColor ( Get ( CurrentDate ) - DueDate; RGB ( 255; 0; 0 ) )

)

Now ... if the task is overdue, the red will display with a leading minus. If you don't want that, adjust the calculation to:

If (

DateOfAction ; DateOfAction - DueDate ;

TextColor ( Abs ( Get ( CurrentDate ) - DueDate ); RGB ( 255; 0; 0 ) )

)

LaRetta :wink2:

[LaRetta]

Actually, I figured out that there were a few instances in which it would break. If there was no DueDate; and you didn't indicate whether the DateOfAction would be restricted to older than or equal current date. I also adjusted it so that, if the DateOfAction WAS later than the current date, it would only count days to the current date (as anything greater would be illogical). So this calculation will cover you (I think). It should be UNSTORED (in Storage Options) and result is number:

If ( DueDate ;

Let (

[

endDate = If ( not DateOfAction ; Get ( CurrentDate ) ; GetAsDate ( Min ( DateOfAction ; Get (CurrentDate ) ) ) ) ;

days = endDate - DueDate

] ;

If ( days < 0 or DateOfAction ; Abs ( days ) ; TextColor ( days ; RGB ( 255 ; 0 ; 0 ) ) )

)

)

LaRetta

[comment]

Actually, since the result is number, you can make it simply:

DueDate - Case ( CompletedDate ; CompletedDate ; Get ( CurrentDate ) )

Overdue results are negative, and you can color them using Format > Number...

[Lee Smith]

If the number results in a minus, you could just use the Number Formatting for the Field, unless I missed something. And since I haven't had my coffee yet, that is always a possibility.

Lee

[comment]

Echo?

[LaRetta]

I didn't see it that simply. Still don't.

Attached comparison ...

1) If task is completed in one day, mine shows black 1. Yours shows -1 and if you use the negative (number format) to make it red, it shows in red when COMPLETED also.

2) If task is NOT completed, mine shows red positive number. Yours shows red negative number.

Users get minus and plus mixed up (as maybe I am) but a simple red if past due and black if NOT past due or done seemed clearer (and seemed to be the request). And if User enters date greater than current date by mistake, mine adjusts it to the current date.

I realize I can get too tired; if so, please straighten me out in the example. But I don't see how the number format will solve his specific request. And I want to understand what it appears I'm missing!

LaRetta

NumDays.zip

[comment]

If a task was due yesterday, and it is completed today, then it is one day overdue. For some reason, your calc shows 0 in such case.

I agree that the minus sign can be a bit confusing. You can choose to to use brackets instead.

[LaRetta]

If due March 15 and completed March 16, my calc shows black 1, which (I thought) was the request - to show the number of days it took to complete - in black. Today (on my system, it is March 16).

Edited March 16, 2006 by Guest
Removed sentence I had repeated in prior post

[comment]

Until OP corrects me, I will go on thinking that due date marks the date on which the task SHOULD be completed. Therefore, "due March 15 and completed March 16" means the task was completed one day LATER than planned. We have no idea how long it actually took to complete. We only know that the goal set was not met.

[LaRetta]

Okay. It's interpretation, I suppose.

Comment: If a task was due yesterday, and it is completed today, then it is one day overdue.

LaRetta: If a task was due yesterday, and it is completed today, then it is no longer overdue and should display black.

The red flag, I thought, was to display that it is overdue and NOT done.

But if you get 0 on my calc (if one day overdue) then we have vs/os issues as well...

[Lee Smith]

I agree, I think it is time for the skynight to provide some feedback. In case no one else has noticed, skynight hasn't been back since the first post.

Lee

[comment]

LaRetta: If a task was due yesterday, and it is completed today, then it is no longer overdue and should display black.

I am not at all sure this is a semantic matter. If a task was not completed on time, then it is overdue, forever. In your version, past tasks that were completed a day ahead of time, appear the same as tasks that were one day late. That is not good for reviewing performance.

----------

CORRECTION:

I got some weird results by inputting dates in the future. Perhaps it doesn't make sense to mark a task as completed a week from now...

[comment]

Who cares, as long as we are having fun?

[LaRetta]

... and LEARNING! :smile2:

[LaRetta]

I got some weird results by inputting dates in the future. Perhaps it doesn't make sense to mark a task as completed a week from now...

If the Due Date is in the future, 3/23/2006, both calcs display 7 (with no completed date). If I insert 3/24/2006 in completed, my calc still shows black 7 and yours shows red -1. Wonky yes - I didn't trap for that. But if the system date (current date) becomes 3/24/2006, my calc will show 1.

Entering DueDate of 3/1 and completed 3/24, my calc displays 15 and yours displays -23. Which is right? How can a task be done before the current date? My assumption is that a task can't be completed in the future ... oh God, I wish that truly WERE possible (smile) so it adjusts. But if you remove the Due Date, your calc shows it is 732394 days overdue. Too many variables and unknowns. Simply put ... a display can't mix and match and suit everyone here. For display (if completion date is restricted to refuse future dates - and it should be), my calc is better (smile). For tracking DaysToComplete report comparisons, yours would be more logical (and screw the color) or whether it is complete or not. But I would remove the minus because the totals would confuse our managers. :beertime:

Edited March 16, 2006 by Guest

[skynight]

I am overwhelmed by the response. It was only last night I posted. I will try some of these things and see if I can make it work. Obviously if it does not it will be because of my inadequacies.

I assume that these formulae work in a calculation field.

Edited March 16, 2006 by Guest

[skynight]

I used what you guys put here, and it works just great, thanks a lot..