Excel Date Problem
Thread Starter
Joined: Feb 2003
Posts: 420
Likes: 0
From: Io
Excel Date Problem
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
Can anyone put me out of my misery?
Thanks in advance.
MF
Official PPRuNe Chaplain
Joined: Apr 2001
Posts: 3,498
Likes: 0
From: Witnesham, Suffolk
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.
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.
