Adding Business days on a date problem

[ecelaras]

Hello !!

The first column contains dates that come from an index function ,in the second column I use the workday function to add working dates ,
Would you know why this happens with the error values ?

14/11/12 #VALUE!
6/11/12 06/19/12
15/11/12 #VALUE!
8/11/12 08/20/12
24/10/12 #VALUE!
22/10/12 #VALUE!
15/11/12 #VALUE!
9/10/12 09/18/12

[Fotis1991]

Hi

Pls share with us the formulas that you use in these 2 columns

[ecelaras]

In the first column I used the Formula : =IFERROR(INDEX(cda, SMALL(IF(FREQUENCY(IF(cda<>"", MATCH(ROW(cda), ROW(cda)), ""), MATCH(ROW(cda), ROW(cda)))>0, MATCH(ROW(cda), ROW(cda)), ""), ROW(I7)), COLUMN(I7)), "")

and in the second coulm : =(workday(cell,1)

[Fotis1991]

Use =ISNUMBER(A1) and copy down to test if ALL cells in column A are numbers(dates) OR some for some reason are text.

Check if exist some spaces.

Try to add in the end of your formula +0.

Or else i think that we need to see how your data looks like and we'll need a sample workbook.

[ecelaras]

I copied the isnumber formula in the source of the data and I get False for all the 6 digit dates but true for the 5digit ones

eg.
11/15/12 false
4/05/12 true

[Fotis1991]

Instead of 0, in the 5 digits date, maybe you have a space or something..

Does =LEN(a1) gives you 5 as result?

[ecelaras]

The 6 digit dates are the problem ,

I used len(a1) and I get 8 for the 6 digit dates and 5 for the 5 digit dates

[ecelaras]

sample.xlsx

Here is a sample

[Fotis1991]

The format in cells that give error, has only 2 digits for the year . Example A1 IS,23/10/12( So in fact is text...). iF you do this 23/10/2012 will get a TRUE .....

[ecelaras]

I changed it and I still get False could it be some configuration Ive made .

Or maybe I changed from dd/mm/yy to mm/dd/yy ??

[Fotis1991]

Use Text to Columns option to get a real date.

Can you handle this?

[ecelaras]

sample2.xlsx

This is the source I copy dates from ,but it is in text how can i make it date ? I use format as date but nothing happens

[ecelaras]

Thanks a Lot Foti !!!!

I got it working with text to column !!!

[Fotis1991]

Glad that i was able to helps you.