skynight Posted March 16, 2006 Share Posted March 16, 2006 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. Link to comment Share on other sites More sharing options...
LaRetta Posted March 16, 2006 Share Posted March 16, 2006 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: Link to comment Share on other sites More sharing options...
LaRetta Posted March 16, 2006 Share Posted March 16, 2006 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 Link to comment Share on other sites More sharing options...
comment Posted March 16, 2006 Share Posted March 16, 2006 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... Link to comment Share on other sites More sharing options...
Lee Smith Posted March 16, 2006 Share Posted March 16, 2006 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 Link to comment Share on other sites More sharing options...
comment Posted March 16, 2006 Share Posted March 16, 2006 Echo? Link to comment Share on other sites More sharing options...
LaRetta Posted March 16, 2006 Share Posted March 16, 2006 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 Link to comment Share on other sites More sharing options...
comment Posted March 16, 2006 Share Posted March 16, 2006 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. Link to comment Share on other sites More sharing options...
LaRetta Posted March 16, 2006 Share Posted March 16, 2006 (edited) 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 Link to comment Share on other sites More sharing options...
comment Posted March 16, 2006 Share Posted March 16, 2006 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. Link to comment Share on other sites More sharing options...
LaRetta Posted March 16, 2006 Share Posted March 16, 2006 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... Link to comment Share on other sites More sharing options...
Lee Smith Posted March 16, 2006 Share Posted March 16, 2006 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 Link to comment Share on other sites More sharing options...
comment Posted March 16, 2006 Share Posted March 16, 2006 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... Link to comment Share on other sites More sharing options...
comment Posted March 16, 2006 Share Posted March 16, 2006 Who cares, as long as we are having fun? Link to comment Share on other sites More sharing options...
LaRetta Posted March 16, 2006 Share Posted March 16, 2006 ... and LEARNING! :smile2: Link to comment Share on other sites More sharing options...
LaRetta Posted March 16, 2006 Share Posted March 16, 2006 (edited) 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 Link to comment Share on other sites More sharing options...
skynight Posted March 16, 2006 Author Share Posted March 16, 2006 (edited) 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 Link to comment Share on other sites More sharing options...
skynight Posted March 18, 2006 Author Share Posted March 18, 2006 I used what you guys put here, and it works just great, thanks a lot.. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6612 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 accountSign in
Already have an account? Sign in here.
Sign In Now