Create your own Order Tracking Excel

Here is a simplified version of my order tracking application. I’

Start out with columns…

• Order Number – you increment this … it’s there for sorting
• Date of Order – The date you placed the order (and paid)
• Category – An arbitrary category used for column filtering
• Item – A short description of the item
• Description – A more detailed description of the item
• Cost – The total cost for the order
• # Items – the number of pieces
• Cost Each – a calculated value of the cost per item
• Vendor – the name of the vendor (sorting and filtering)
• Sent Date – the date that the vendor tells you the order was sent
• TTS Days – Time To Send days (calculated)
• ETA – The date that the vendor estimates for delivery
• ETA Days – the estimated number of days to deliver (calculated)
• Arrived Date – the date that the item actually arrived
• ETA/ATA Difference – the difference between estimated and actual (calculated)
• Travel Time – the time that the order was in transit (calculated)

Now to put in some calculations … This is where the value is added. I’m using Excel 2010, so these formulae are using functions that I know are there in this version … don’t blame me if your spread-sheet doesn’t have the DATEDIF function!

Cell Reference	Formula	Purpose
H2	=F2/G2	calculates the cost per item
K2	=IF(J2<>"",DATEDIF(B2,J2,"D"),"")	Calculates the number of days to send the order
M2	=IF(L2<>"",DATEDIF(B2,L2,"D"),"")	Calculates the estimated number of days to arrive
O2	=IF(N2<>"",IF(N2>L2,DATEDIF(L2,N2,"D"),DATEDIF(N2,L2,"D")),"")	Calculates the difference between the Estimated and Actual time to arrive days
P2	=IF(N2 <> "", IF(N2>J2,DATEDIF(J2,N2,"D"),DATEDIF(N2,J2,"D")),"")	Calculates the number of days for the order to arrive

Copy the formulae down the columns in the spread-sheet and you should be good to go.
You can turn on Filtering in the spread-sheet so that you can track individual vendors and item categories.
When you don’t enter an ordered date, the item is, effectively, a wish-list item.
Well … that’s my piece for today. Hopefully, I’ll have some free time on the weekend to carry on with my LED bedside light.

Here is a copy of the spread-sheet for personal use.