Calculate due date based on multiple criteria in Excel 2007

[allienzaddicts]

Hi everyone!

Am a newbie here as well as Excel
I really need help in solving my due date calculation based on certain criteria.
I need to calculate if the task I'm working on has met the target or not based on the complexity of the task. Each complexity has been set with number of days to be completed.

Task complexity = Low (2 days), Average (3 days) and High (4 days)

I have a counter for the task start/end date and time which returned the number of days and hours spent to complete each task in this format: 0 day(s) 2 hour(s) and 21 min using NETWORKDAYS function.

How do I validate to see if the task has met the target based on the complexity and the number of days set? Example: If the complexity is Low and task duration is less than 16 hours (2 days) then return "Yes" and this should loop to validate the other criteria. I have tried using several basic formulas but it doesn't work fully or completely not working at all

Please help!! I have been pulling my hair while trying to solve this problem.

Many thanks in advance!

[etaf]

can you attach a sample sheet

IF( AND ( cell with task value = "low", cell with the hours <16 ) , "yes" , IF( next test

but it depends on your data, format and layout

[daddylonglegs]

I think you have a problem because the result "0 day(s) 2 hour(s) and 21 min" looks like a text value. As such it's difficult to "query" that value to do what you want. I think it would be better if you can extract the duration as a number (representing days), e.g. 2.5 for 2 1/2 days, then you can easily construct a formula to see whether your target has been met.

Which formula currently gives you the result 0 day(s) 2 hour(s) and 21 min ?

Are you counting all hours on weekdays or just 9 to 5 or similar?

Can your start/end dates be outside the working hours, e.g. starts on Sunday but you only count the time from Monday?

[allienzaddicts]

Hi etaf, thank you for your feedback. I have tried using nested IF(AND( but it doesn't work for all task. Some returned "Yes" even when it has actually missed the target. Maybe I did it wrongly. As per request - I have attached a sample workbook for what I'm working on right now.

Thank you for your help.

[allienzaddicts]

@daddylonglegs

I too suspect it could be due to that. But I've been trying to convert those results into what you're suggesting but I've failed to get what I want. I've tried converting the result by using formula =A1*3 or =A1/"8:00" (consider A1 is the result cell) and the answer I got was totally not what I'm looking for. You may refer to the sample file I've attached here for the NETWORKDAYS function I used to get the handling time. And yes, my End date especially may be outside the working hours.

Thank you so much for your help!

[daddylonglegs]

OK, I see that your column G values are not text, so the formula there is fine.......but I wouldn't format as you have done because I think you are getting the wrong results - in row 15 for instance the result says "1 day 22 hours 50 mins"....but that's only right if a day is 24 hours, as your "day" is 14:00 to 23:00 then that's 46 hours 50 minutes, in fact more than 5 days - wouldn't you want that to fail the target?

I would format that column as [h]:mm to see just hours and minutes and then use this formula in H10 copied down

=IF(G10*24<=VLOOKUP(D10,F$21:H$23,3,0),"Achieved","Missed")

Also I don't see why 2 days is 16 hours, 3 days is 24 hours etc. surely if hours are 14:00 to 23:00 then 1 day is 9 hours?

[allienzaddicts]

@daddylonglegs

You're spot on mate! OMG! I didn't even noticed my working hours is actually 9 hours. LOL!

And your formula works like a charm

Thank you so much for helping in solving my 2 weeks misery. You're the star!!