Is there a way to report on tasks - time overdue, number of times job extended from original date, average time for completion per category of task for individual users etc.
Outlook doesn't track extensions and date changes, but you can view the number days overdue and days to complete each task using custom fields.
For this we combine the IIF and DateDiff functions:
Iff(expression, do if true, do if false)
and
DateDiff(time period, date1, date2)
Formula fields can't be grouped or sorted.
If the task is not marked complete, get the difference (in days) between the due date and now. If it's marked complete, enter "complete" in the field. Use Now() as the first date to return negative numbers; use [Due Date] first to return a positive number.
IIf([Complete]=False,DateDiff("d",Now(),[Due Date]),"Complete")This formula checks to see if the task is complete and if so, gets the number of days between the start date and now. If it's not complete, 'Not Done' is entered in the field.
IIf([Complete]=True,DateDiff("d",[Start Date],[Date Completed]), "Not Done")What if there is no start date to calculate from? Easy, just add another IIF statement. This formula checks to see if task is complete and if so, it checks to see if there is a start date. If current date is found, it calculates the difference using the start date, otherwise, it uses the Due date.
IIf([Complete]=True,IIf([Start Date]>1/1/3000,DateDiff("d",[Due Date],[Date Completed]),DateDiff("d",[Start Date],[Date Completed])),"Not Done")Outlook doesn't use NULL or "" (blank) when a date field is left empty; it uses 1/1/4501. So, to check for the presence of a date, look for values greater than 1/1/4501, or any future date, as I do in this formula.
The formulas above are used in two separate fields, one for overdue and one for days to completion, but you can use one field for both. However, the formula will be a little more complicated.
IIf([Complete]=True,IIf([Start Date]>1/1/3000,DateDiff("d",[Due Date],[Date Completed]),DateDiff("d",[Start Date],[Date Completed])),DateDiff("d",Now(),[Due Date]))If you want the overdue values marked as overdue, you can add text to a value using & "text" format. If you plan on working with these values in Excel, don't use this.
IIf([Complete]=True,IIf([Start Date]>1/1/3000,DateDiff("d",[Due Date],[Date Completed]),DateDiff("d",[Start Date],[Date Completed])),DateDiff("d",[Due Date],Now()) & "Overdue")
Paresh Lodha says
Thank you so much Diane! this was quite helpful. I was looking to give my team members something in Outlook in which they can themselves see if they have delayed or completed the task on time.
Very helpful. Appreciate your help! :)
Thanks!
Deanne says
"Calculate days overdue and days to complete tasks" - is this function available in Outlook 2016 version?
Diane Poremsky says
the formula will work in all versions of outlook. if you don't know how to create custom fields, the first 7 steps here show you how:
https://www.slipstick.com/outlook/rules/custom-field-formulas-phone-numbers/
Murugan says
Hi Diane,
I need your help in calculating networkdays in outlook in custom field. I have succeeded in excel but failed to incorporate in outlook. After a research, i realized that there is no in-built function called Networkdays, median and mod. below is my formulae.
(NETWORKDAYS([Received],[Last Verb Exec Time])-1)*("21:30"-"12:30")+IF(NETWORKDAYS([Last Verb Exec Time],[Last Verb Exec Time]),MEDIAN(MOD([Last Verb Exec Time],1),"21:30","12:30"),"21:30")-MEDIAN(NETWORKDAYS([Received],[Received])*MOD([Received],1),"21:30","12:30").
Please help
Diane Poremsky says
Yeah, Outlook doesn't do network day/work days. NETWORKDAYS is not a supported function. You could use date diff then subtract 2 days for every 5.
If you can use VBA, you can use a function chip pearson wrote - i use it in the sample here - https://www.slipstick.com/developer/create-outlook-appointments-for-every-xx-weekday-using-vba/