Excel Date Problem
Thread Starter
Join Date: Feb 2003
Location: Io
Posts: 420
Likes: 0
Received 0 Likes
on
0 Posts
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
Join Date: Apr 2001
Location: Witnesham, Suffolk
Age: 80
Posts: 3,498
Likes: 0
Received 0 Likes
on
0 Posts
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.
