Due Date Calculation

[ECorr]

Hi- I am developing a spreadsheet to help track requests from clients. We have a color coding system that determines the number of hours they can expect a response from us. We have 4, 24, 36 or 48 hours, to help prioritize.

I need to develop a formula that:

1. calculates the date/time something is expected to be responded to
2. calculates that based on the expected turnaround (hours) defined in another cell
3. I need it to understand workdays and working hours

See attached for what I am trying to do.

Appreciate your help!

Thanks,
E

[ranman256]

Here you can set the calculated hours in the cell
H5 = HRsAdd(C5,G5)

and extra code to colorize the cell when you type the word RED, or PURPLE into F column

col F should really be a drop box, and the hrs should not be a formula IF(F2=RED, ORANGE....
The drop box should read from a separate sheet , with colors,hrs.....a VLookup()
but if the IF works then go for it.

Please Login or Register  to view this content.

[ECorr]

Thanks for this, though I'm not sure I want to write a macro code. Unless that's the only way to do what I'm looking to do. Appreciate your time in writing this for me, but hoping there's a simpler way.

[ranman256]

for a formula, lookup 'adding time in excel'

[ChemistB]

Okay, this is pretty complex but I think it takes everything into account. (For a better formula, I would reach out to DaddyLonglegs of this forum. He's the time master)

=IF(OR(MOD(C5+G5/24,1) < "8:00"+0, MOD(C5+G5/24,1) >"18:00"+0), IF(WEEKDAY(INT(C5+G5/24)+1,2)>5, INT(C5+G5/24)+1-WEEKDAY(C5+G5/24+1,2)+8+"8:00", INT(C5+G5/24)+1+"8:00"), IF(WEEKDAY(INT(C5+G5/24),2)>5, C5+G5/24-WEEKDAY(C5+G5/24,2)+8, C5+G5/24))
Does that work for you?
I assume that if it expires on weekend, then it's due first thing Monday.

[ECorr]

This appears to work just fine! I definitely appreciate your help on this!! I'll let you know if run into any problems..

[ECorr]

One more question. See attached. I am now trying to automate it to say "based on today's date/time, and the expected due date, what would the color code be"

See attached for what I'm trying to do. I left my current formulas in there, as i got them to work to some degree? but for some reason, line 7 isn't calculating properly. It should calculate as "RED" but it is just returning as "FALSE"

really appreciate it!

[ChemistB]

Now() is not greater than 8/12/15 3:31 PM therefore it is not red. It does not meet any of your criteria.

You will also never get a yellow or a blue with your formula.
If Now + 1 is less than H8, it will stop at orange, if Now +1 is not less than H8, then neither will be Now + 1.5 or Now + 2

You need to rethink your logic.

[ECorr]

Sorry should have specified what happens when I change it.

If I change the formula in cell L7 to say less than NOW(), it does work- however, if I incorporate that into the remaining cells for that column- cells L6 and L8 return a FALSE as well. And then L5 returns as RED- which I don't understand because 8/14 8am is not less than or equal to NOW(). It should return as ORANGE.

[ChemistB]

What is your ranges for red, orange, yellow and blue?
Are they based on Due Date/Time - Now()?

[ECorr]

So to explain a little further, I've told a client that for an email categorized as RED, we will respond in 4hrs. So technically, if it's RED, it should be responded to that same day, unless, of course, they submit a RED request at 2:01pm or later, it may spill into the following day. So something that is RED should essentially stay RED in column L. For ORANGE, we've told them we will get back to them in 24hrs or less- so, if we didn't respond that same day, the following day would re-categorize to RED. and so on and so forth.

RED = 4hr turn around
ORANGE = 24hr
YELLOW = 36hr
BLUE = 48hr

If the way the formula is written, doesn't make sense, then we can rewrite of course.

[ChemistB]

So if it's now within 4 hours, even if it was originally a 36 hour turnaround, it should be characterized as Red now?

[ECorr]

that is correct

[ChemistB]

Okay, try this. I made a little table in F17:G21 (put this off to the side or on other sheet)

Please Login or Register  to view this content.

Then in L5

=VLOOKUP((NETWORKDAYS(NOW(), H5)-1+MOD(H5,1)-MOD(NOW(),1))*24,$F$17:$G$21,2)

[ECorr]

You are amazing. Thanks so much!