Calculating Turn Around Time by date

[JCL622]

Hi, firstly thank you for all the help, ever since finishing undergrad I haven't had much need to work with excel till now, and I find that I am severely deficient with the formulas and rules in excel.

I need a bit of help with helping to figure out how to make a turn around time formula for my excel sheet. Infinia Patient Scheduler Start.xls

Basically what I need to calculate goes like this.
T1 = 6 working days = meaning patient can get scheduled on the 8th day.
T2 = 8 working days = meaning patient can get scheduled on the 10th day
T3 = 12 working days = meaning patient can get scheduled on the 14th working day.


So in a rough scenario - if the package leaves the doctor's office on Monday, 4/23/2012 - T1 should be May 3, 2012. T2 should then be May 7, 2012. T3 would then be May 11, 2012


Is this even possible to do through excel? Please help! I find myself going nuts because I lose my place when I'm counting by hand.

[daddylonglegs]

I would include the year, e.g. put 2012 in A1 then try this formula in B3

=IF(ISNUMBER(A3),WORKDAY(A3&LOOKUP("zzz",A$2:A3)&A$1,8),"")

format B3 in required date format then copy down the column

Copy the formula into columns C and D, changing 8 to 10 and 14

[JCL622]

I would include the year, e.g. put 2012 in A1 then try this formula in B3

=IF(ISNUMBER(A3),WORKDAY(A3&LOOKUP("zzz",A$2:A3)&A$1,8),"")

format B3 in required date format then copy down the column

Copy the formula into columns C and D, changing 8 to 10 and 14

Thank you for such a quick reply!! I will try this immediately! Just a quick question though, what do you mean by adding 2012 to A1? Do you mean to format the entire column into a date? haha, sorry, I know this is probably elementary but I am quite hopeless with excel

[JCL622]

Ah, ok, I tried it and even with out adding 2012, it works! Now, out of curiosity, how does the excel sheet know the month and year? All I've done is list a bunch of random numbers, yet the calendar is showing the correct month after, and the correct year.... so interesting!

[daddylonglegs]

You could probably make it simpler by making column A into dates (however displayed) but my solution supposes that you change nothing except add the year to cell A1 only.

If you don't specify the year then it will assume the current year, so that might be OK now but as soon as you get to 1st jan 2013 it will assume 2013. If you want to control the year change yourself then better to put in in A1 (or use any other cell but change the A1 in the formula)

[JCL622]

Thank you so much! This is perfect!

So, if i add the year into the column, that would mean if I were to change it to 2013 it would do everything from 2013? - Out of curiosity, how does it know which month is being listed?

Also, I have a bit of a problem, I'm supposed to also add an "exception" rule for hypothetical holidays - how do I do that?

For example: May 28th is an off day.
So if something came in on May 25 - (because May 28th is an off day, it can't count it as part of the working day time.

For 2012 I would have to nix out May 28, July 4, Sept 3, Nov 23-24, Dec 24-25.

I am in awe of how much you can do with excel, and amazed that there is a community of excel experts. Thank you so much for helping me out already.

[JCL622]

Sorry, I'm also trying to figure out, how is this formula figuring out what date/year/month it is?