End date calculator - very frustrated!

[JoD]

This is the first time I've run across an idea I've had that I've not been able to make work in Excel, and I'd love to hear if there's an obvious fix that I'm missing, or if I'm just going about this entirely the wrong way. What I'm trying to do is create a calculator to figure out a contractor's end date based on their starting date and the number of working days they will complete.

The method I'm currently using involves two worksheets. The second one (Lookup) simply has a list of working days this year in column A - that is, no weekends or public holidays are in it. The first sheet is supposed to do the actual calculation, but I cannot get it to work. I have two cells labelled for the Start Date (C3) and Number of Working Days (C4), both numbers the user would need to input. Then there's a third cell which is supposed to calculate and show the end date - and that's where it all goes wrong. My intention was to use OFFSET with some sort of nested formula that would match the value in C3 with something in column A of Lookup and return the cell address... but nothing seems to work.

Here's the current formula I'm trying to use. I just get a message saying there's an error with my formula - it doesn't even go through the calculation steps:

=OFFSET(ADDRESS(MATCH(C3,Lookup!A1:Lookup!A253,0),1,1,TRUE,"Lookup"),C4-1,0,1,1)

Now, if I put the nested part into a cell alone, it returns the correct cell addresses. If the start date is 02/01/2007 (the first working day this year), the ADDRESS formula correctly identifies cell Lookup!$A$1. There's no problem with the base OFFSET formula, either - it returns the correct end date if I replace the nested formula with a cell address. It just seems like OFFSET won't let me use a nested formula to return a cell address.


If anyone has any ideas on how to make this work, or even an entirely different idea that would let me calculate contract end dates like this, I would be very thankful!

 

[Lewiy]

You do not need a list of all the working days, just a list of all the holidays (not including weekends).

Let’s say the start date is in A1 and the number of working days is in B1 and the list of holiday dates is in C1:C10. Use the following:

=WORKDAY(A1,B1,C1:C10)

To give you the end date.

Note: You will need to install the Analysis TooPak add-in to use the WORKDAY function.

 

[Anthony47]

I suggest that you use the function WORKDAY, for example:
=WORKDAY(Datestart,NumbOfDays,Z1:Z20)
In this example Z1:Z20 is used to list all the public holidays that will be included in the calculation (ie the deadline will mov forward).

If you get #NAME it means that the function is not installed; you need to install the Additional component "Analysis tool" (Tools /Additional components; I am not sure about the name of the additional component in English).

Daoes it help? Bye.

 

[barry houdini]

WORKDAY would also be my preferred option but the reason your method doesn't work is because ADDRESS function returns a text string representing the address, not a cell reference that OFFSET can use, try INDEX, i.e.

=INDEX(Lookup!A1:A253,MATCH(C3,Lookup!A1:A253,0)+C4-1)

I'm assuming that C3 would always be in your list, i.e. it's a workday

 

[JoD]

Thanks all - when I was searching around for an answer to this I didn't find any mention of the Analysis Toolpak and WORKDAY function - didn't even know it existed! That should be fine.

barry houdini - I did wonder if that might be the problem, but the error threw me. I'd tried many different formulas, and one of them was clearly returning a text string instead of the straight cell reference; the formula would calculate correctly down to the last step, then fail. It was fairly clear what the problem was with that one, as just step before last it placed the cell reference in quotation marks. So when I got an error telling me there was a problem with my formula and not even letting me see the calculation steps, I thought it might be a different issue.

Anyway, all sorted now, thanks again.