calculating aged days

**jm1234** · 09-27-2007, 09:35 PM

I'm trying to "easily" compute days that a certain type of inventory has been stored in our warehouse. Our database software doesn't have a report that details this.

I have the raw data downloaded to excel, but need to find a way to rearrange it and allow for easy viewing.

As seen in the copy below I have an ID Tag with a date it was received and then a corresponding date that it left our warehouse. What I need is an easy way to figure out how long each ID Tag was in our warehouse.

Would it be best to use a pivot table or is there other options that would work. I'm dealing with close to 3,000 tags!

Thanks for the help

TAG ID REF DESCR DATE REC ISSUED

15021749 58051 Receive 6/25 90 0
15021749 G 8443 Ship 7/8 0 90

15021750 58051 Receive 6/25 90 0
15021750 G 8443 Ship 6/26 0 90

15021751 58051 Receive 6/25 90 0
15021751 G 8443 Ship 6/25 0 90

15021752 58051 Receive 6/25 90 0
15021752 G 8443 Ship 6/26 0 90

**rylo** · 09-27-2007, 09:57 PM

Hi

Say your data is in columns A:F, with the first 2 entries (15021749) in rows 3 and 4 then
H4: =DATEDIF(D3,D4,"d")

Copy this formula down to the "Ship" rows of the rest of your data.

HTH

rylo

**fletch396** · 09-28-2007, 07:21 AM

hows this....

**daddylonglegs** · 09-28-2007, 07:24 AM

To get the difference in days between one date and another just subtract the earlier date from the later, e.g.

=B1-A1

format as general

where B1 is the later date

**jm1234** · 09-28-2007, 02:59 PM

Thank you everyone for the help, it is very much appreciated. I attached the raw data I receive, to see if anyone can figure out an easier way to apply the date function to it. Right now I am just sorting the data by DESCR then copying the function thru all of the "ship" DESCR column. After that I resort back to normal (by TAG ID, ascending).

It just means I have to review each row to see if quantities shipped out on mulitple dates after the received date and adjust the function accordingly. I can then send the "Aged Days" column to a graph that displays how many boxes were stored for: 1-7 days, 8-14 days, etc.

Is there an easier way to go about this? Again thanks to everyone for the help, sometimes after looking at a screen for hours, I just need an outside perspective!

J