Find end date

**jworth** · 10-02-2007, 11:09 PM

My brain is so very tired and I need help please.....

I need to calculate what the end date will be, minus workdays and holidays:
A1 - start date - 1/1/2007
B1 - 60 (days) - 60
C1 - end date - ???

I've tried everything I've read from the hundreds of Excel Help sites I've visited. I'm so very frustrated and would really appreciate any help.

Thank you in advance!

**rylo** · 10-02-2007, 11:28 PM

Hi

Do you mean minus weekends and holidays??? If you remove workdays and holidays, then what else is left???

rylo

**jworth** · 10-03-2007, 08:23 AM

Originally Posted by rylo

Hi

Do you mean minus weekends and holidays??? If you remove workdays and holidays, then what else is left???

rylo

Yes, I'm sorry - that is what I meant, minus weekends and holidays.

**daddylonglegs** · 10-03-2007, 08:26 AM

Try using the WORKDAY function from Analysis ToolPak add-in.

If you want to exclude holidays you'll need to list the dates, assuming you list holiday dates in Z1:Z10 you can use the formula

=WORKDAY(A1,B1,Z1:Z10)

**jworth** · 10-03-2007, 08:46 AM

Originally Posted by daddylonglegs

Try using the WORKDAY function from Analysis ToolPak add-in.

If you want to exclude holidays you'll need to list the dates, assuming you list holiday dates in Z1:Z10 you can use the formula

=WORKDAY(A1,B1,Z1:Z10)

I do have my list of Holiday dates. I changed your formula to match my cells.
I still get the error #NAME$.

**daddylonglegs** · 10-03-2007, 09:12 AM

You need to install Analysis ToolPak add-in

Go to Tools menu,select "Add-ins" and under that tick "Analysis ToolPak" box then re-type formula

**jworth** · 10-03-2007, 09:36 AM

Originally Posted by daddylonglegs

You need to install Analysis ToolPak add-in

Go to Tools menu,select "Add-ins" and under that tick "Analysis ToolPak" box then re-type formula

I do have the Add-in installed. That's one of the things I did before I posted on this site.

**daddylonglegs** · 10-03-2007, 09:55 AM

OK then, #NAME? error would normally be generated if you mis-spell a function or if you use a named range which doesn't exist. Is your holiday range named or are you just referencing a range of cells?

Can you post the exact formula you tried?

**jworth** · 10-03-2007, 10:14 AM

Originally Posted by daddylonglegs

OK then, #NAME? error would normally be generated if you mis-spell a function or if you use a named range which doesn't exist. Is your holiday range named or are you just referencing a range of cells?

Can you post the exact formula you tried?

I'm really not usually this stupid.....The error is #VALUE! , not #NAME?. Is there a way to post my actual Excel file? Formula I'm using:

=WORKDAY(C6,C8,G4:G17)

cell C6 is my start date
cell C8 is number of days
cell C10 is the above formula with #VALUE! error.
cells G4:G17 are the dates of my holidays.

**daddylonglegs** · 10-03-2007, 10:29 AM

The formula looks fine, it still looks like you might not have Analysis ToolPak installed.

Try uninstalling and re-installing Analysis ToolPak. Make sure you tick Analysis ToolPak and not Analysis ToolPak VBA.

Check that other Analysis ToolPak functions work, e.g. Try

=EDATE(TODAY(),1)

and

RANDBETWEEN(1,10)

These shouldn't return errors

Does the formula work without the holiday range, i.e.

=WORKDAY(C6,C8)

Make sure C6 contains a date and C8 a number

**jworth** · 10-03-2007, 10:55 AM

Originally Posted by daddylonglegs

The formula looks fine, it still looks like you might not have Analysis ToolPak installed.

Try uninstalling and re-installing Analysis ToolPak. Make sure you tick Analysis ToolPak and not Analysis ToolPak VBA.

Check that other Analysis ToolPak functions work, e.g. Try

=EDATE(TODAY(),1)

and

RANDBETWEEN(1,10)

These shouldn't return errors

Does the formula work without the holiday range, i.e.

=WORKDAY(C6,C8)

Make sure C6 contains a date and C8 a number

I ended up uninstalling Excel all together, RESTARTED, reinstalled Excel, then installed the Analysis Toolpak again. I even re-entered the formula.

=EDATE(TODAY(),1) gives me 11/3/2007
=RANDBETWEEN(1,10) gives me 9
=WORKDAY(C6,C8) gives me 3/27/2007
as soon as I add the holiday cells back in it gives me the error.

**daddylonglegs** · 10-03-2007, 11:09 AM

How are the holiday dates generated, did you just type them in or are they generated by formulas? You can have true blank cells in that range but a "formula blank" or even a space (both of which you can't see) will generate an error for your WORKDAY formula

**jworth** · 10-03-2007, 12:13 PM

Originally Posted by daddylonglegs

How are the holiday dates generated, did you just type them in or are they generated by formulas? You can have true blank cells in that range but a "formula blank" or even a space (both of which you can't see) will generate an error for your WORKDAY formula

I just typed them in myself in cells G4:G17. Example:

01/01/2007
01/15/2007
2/19/2007
4/6/2007
5/28/2007
7/4/2007
etc...

**daddylonglegs** · 10-03-2007, 02:15 PM

If there's any text in the date range you'll get a #VALUE! error. I see your dates are in m/d/yy format. If your regional settings are set to dd/mm/yy format then a "date" entered like 01/15/2007 might be interpreted as text (not a date) because, obviously, month cannot be greater than 12.

You can check whether a date is recognised as such. Assuming 01/15/2007 is in G5 try the formula =ISNUMBER(G5) in another cell. If it's a date this should return TRUE.

If this is the problem then you can change your regional settings through the control panel, but doing so may affect more than just Excel

**jworth** · 10-03-2007, 02:41 PM

Originally Posted by daddylonglegs

If there's any text in the date range you'll get a #VALUE! error. I see your dates are in m/d/yy format. If your regional settings are set to dd/mm/yy format then a "date" entered like 01/15/2007 might be interpreted as text (not a date) because, obviously, month cannot be greater than 12.

You can check whether a date is recognised as such. Assuming 01/15/2007 is in G5 try the formula =ISNUMBER(G5) in another cell. If it's a date this should return TRUE.

If this is the problem then you can change your regional settings through the control panel, but doing so may affect more than just Excel

I put the formula you told me =isnumber(g5) in a different cell and it resulted in the word TRUE.

**daddylonglegs** · 10-03-2007, 07:58 PM

Well I'm not sure I can suggest much more.....

Perhaps adjust the holiday range downwards a cell at a time, i.e. use G4:G16 then G4:G15 until you don't get an error, this might pinpoint the "bad" date as it looks like you have at least one date in there that Excel doesn't think is a date.

If you still can't find the problem perhaps try putting the dates in a different range or, if still no joy, can you post your worksheet?

**jworth** · 10-04-2007, 07:22 AM

Originally Posted by daddylonglegs

Well I'm not sure I can suggest much more.....

Perhaps adjust the holiday range downwards a cell at a time, i.e. use G4:G16 then G4:G15 until you don't get an error, this might pinpoint the "bad" date as it looks like you have at least one date in there that Excel doesn't think is a date.

If you still can't find the problem perhaps try putting the dates in a different range or, if still no joy, can you post your worksheet?

I did try to add just the first date in my Holidays and it did nothing....
Attached is the file. Thank you for looking at it.