enhance formula

[bigfoot]

i run a report on a monthly basis. column A has an employee number, of which there are about 100, and column C, a date.the dates are entered daily, if an employee. makes an entry for that day. all employees and dates are in one file. i review the column of dates, looking for a missing date. i found a formula, here, that i use, and works for me. =IF(C3=C2+1,"","C2+1"). it works well if one day is missing, but it does not help me if multiple consecutive days are missing. can anyone suggest an enhancement to the formula, to pick up if multiple days are missing. thanks

 

[West Man]

Try: =IF(C2=C1+1,"",IF(C2-C1=2,C1+1,TEXT(C1+1,"mm/dd/yy")&" thru "&TEXT(C2-1,"mm/dd/yy")))

 

[bigfoot]

thanks, i will give it a try, first thing in the morning.

 

[bigfoot]

yes, it works. thanks alot. when there are multiple days misssing, the result is a "from date" thru an "end date". is there any way to list each day in a list format? thanks.

 

[West Man]

I can not think of a practical way of doing so.

 

[West Man]

If multiple days are missing, would it be acceptable to have the missing dates in seperate cells on the same row as opposed to all in one cell?

 

[T. Valko]

i run a report on a monthly basis. column A has an employee number, of which there are about 100, and column C, a date.the dates are entered daily, if an employee. makes an entry for that day. all employees and dates are in one file. i review the column of dates, looking for a missing date. i found a formula, here, that i use, and works for me. =IF(C3=C2+1,"","C2+1"). it works well if one day is missing, but it does not help me if multiple consecutive days are missing. can anyone suggest an enhancement to the formula, to pick up if multiple days are missing. thanks

If this is a "monthly" file does that mean the dates will only run from the 1st of the month to the end of the month for the specific month?

So, for June the dates will only be from 6/1/2011 to 6/30/2011?

And you want to know which dates have not been entered in column C?

 

[bigfoot]

thanks for response. each report runs for a calendar month, first day to last. no prob going across. however need space between columns, so i can associate a number from column A, with each date, as the same date can appear 50 times. thanks.

 

[bigfoot]

any further thoughts on this would be appreciated.

 

[West Man]

If you could post a small sample of your data it would be helpful. Your comment that there could be the same date appearing many times is confusing. Neither your original formula nor my suggestion handles duplicate dates.