Custom cell formatting & Completion time based on criteria check

[Danyal]

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!

[teylyn]

The custom format [h] will return the number of hours even if greater than 24. If you want to show days, you probably don't want [h] showing more than 24, i.e. 25 hrs would be 1 day and 1:00. The custom format for that is

d "day(s)" hh:mm

The results for your data sample are

31 day(s) 01:00
0 day(s) 01:21
0 day(s) 11:34


For the second question: you need to enter proper time values into the cells B11, B12 and B13, like

10:00
100:00:00
200:00:00

Now you can use this formula in cell E2 and copy down:

=IF(C2>VLOOKUP(D2,$A$11:$B$13,2),"outside of time frame","within time frame")

See attached file.

cheers, teylyn

[Danyal]

What a prompt response! It worked like a charm. Thank you very much for the help Teylyn

Much appreciated.