Good day members,
I'm currently trying to finalize a report for my department and am running into a rather basic issue.
I need to calculate the time it took for a task to be completed. The data we extract is in the following format.
Task type 2 (specified in lets say column D2)
Task start time: 6/4/2015 23:35 (lets say its in cell A2)
Task resolution time: 7/24/2015 12:26 (lets say this is in cell B2)
* I just need to calculate the result in another column (B1-A1 result in C1) and give the result in days, hours and minutes. the typical custom cell formatting method of [h]:mm works here to give me the resolution time in total hours and minutes. however what cell formatting would I need to get this in days, hours and mins. I tried using the cell formatting mm : dd : hh : mm but this gives me an incorrect response. For the query above my result should be roughly 50 days and some hours but when I use the afore-mentioned format I get a result like 02 : 18 : etc : etc (which means two months, 18 days?).
* The second query is I need to check whether this task was completed within the specified timeline for that particular task. I'd like a formula to match the task type in column D with its specific turnaround time in another table and then give a response based on whether it was completed within the correct time frame or not. the result would just need to be in the form of a true / false answer.
The second table against which the task type completion time is check could be very simple. there would be one column (lets say column A specifying the task type and the second column would specify the time it must be completed within).
I've attached a small data file to try and illustrate my query. I hope it sufficient articulates my query.
Many thanks in advance for any help!
Bookmarks