End date calculator - very frustrated!

JoD

New Member
Joined
Jul 25, 2007
Messages
5
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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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:
Code:
=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

Well-known Member
Joined
Mar 29, 2006
Messages
2,274
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Jul 25, 2007
Messages
5
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. :)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,561
Messages
5,765,109
Members
425,260
Latest member
worldbfreebase

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top