Excel Date Problem

[Maxflyer]

I am working on a spreadsheet that has amongst other things, two columns of dates - "Date In" and Date Out". I have another column that simply calculates the number of days between IN and OUT. I need to have a seperate cell that shows the average number of days based on the information I have already calculated. This is where I am struggling. I thought it would simply be a matter of totalling the number of days coulmn and dividing by the number of date lines. This doesn't work, I keep getting a VALUE error in the cell.

Can anyone put me out of my misery?

Thanks in advance.

MF

[Duckbutt]

How about =AVERAGE(A1:A10), inserting the appropriate cell references?

[Keef]

It depends on how you are doing it. The DATE in Excel is actually a number - years since 1/1/1900 (which is "1" in this context) with lots of decimals to take care of months, weeks, days, hours, etc. The "date" number format then converts this to the date we recognise.

If you subtract one date from another, the answer will (unless you reformat it) appear as a third date - and look odd.

You should be able to add those "differences", and divide them by the ROWS() count, and so on. My logbook certainly does that with no bother.

If all else fails, put the spreadsheet on a website and PM me the URL, and I'll take a look at it.

None of this works for dates before 1/1/1900. Excel will display them correctly, but won't recognise them as a date and won't process them in the same way. It can be done, but you need to extract day, month, and year and do your own calculations from those.

[Maxflyer]

Duckbutt

So simple, yet so effective. I'm just going off to bang my head against a brick wall for being so stupid.

Thanks

MF