I need some EXCEL help

[yvr girl]

I am trying to figure out how long things are in inventory. I'm not having any problem with items that have a start date and and end date. I can't get things still in inventory to give me a running count. I've tried referencing a cell that has TODAY() in it and also referencing a cell that has today's actual date 24-09-06 in it.

Here's the formula I used: =IF(H2=" ",$L$1-A2,H2-A2)

H2= Sold (end) date
$L$1= today's date
A2= Inventory (start) date

If I have a sold date it works, but if I don't I get a number like -38890. For some reason it isn't converting the result to a number of days.

HELP!

[matcom]

1. make sure the format of H2 is in a "Date" format

n/t

[yvr girl]

3. all date cells are in date format

[Radical Activist]

2. I'm not that good with excel, but

You might try making sure all the cells are set to use the same format for the date. Sometimes I find different cells using different date formats for some unexplained reason. If you've already done that and your problem is more complex, then I'm sorry for being redundant.

[billyskank]

4. Just a wild guess but

should it be:

=IF(H2="",$L$1-A2,H2-A2)

?

I just noticed you have a space where you are checking if the field is blank. Is that how it works or is that the problem?

[yvr girl]

5. You are a god!

Thank you! Problem fixed. :D

[billyskank]

6. Cool!

And I don't even know how to use Excel. :D

:hi:

[yvr girl]

7. smarty pants

:hi:

[Moochy]

8. Number of days since 1900-Jan-0

When you view a date as an integer in excel it is stored as two parst, one part is the number of days passed since Jan 1st,1900, and the rest is the optional time component.

http://www.cpearson.com/excel/datetime.htm

Actually, this number is one greater than the actual number of days. This is because Excel behaves as if the date 1900-Feb-29 existed. It did not. The year 1900 was not a leap year (the year 2000 is a leap year). In Excel, the day after 1900-Feb-28 is 1900-Feb-29. In reality, the day after 1900-Feb-28 was 1900-Mar-1 . This is not a "bug". Indeed, it is by design. Excel works this way because it was truly a bug in Lotus 123. When Excel was introduced, 123 has nearly the entire market for spreadsheet software. Microsoft decided to continue Lotus' bug, in order to fully compatible. Users who switched from 123 to Excel would not have to make any changes to their data. As long as all your dates later than 1900-Mar-1, this should be of no concern.