Inventory Freight Calculation

To account for freight on incoming inventory, I allocate the freight charges proportionately over the inventory items by total value. Quickbooks doesn’t have a way to handle that, so I use a simple Excel spreadsheet to do the calculation.

The last entry is always the freight (or handling or whatever I want to distribute). I don’t [...]

Read Full Post »

Calculating a Date Based on Year, Week Number and Day of the Week

Use the DATE and WEEKDAY functions as shown in the following formula:
=DATE(C2,1,3)-WEEKDAY(DATE(C2,1,3),1)+7*(B2-1)+A2

Read Full Post »

Entering dates quickly, without having to use delimiters to separate month, day, and year.

nter only the \”day\” part of the date and complete it by using the DATE, YEAR, TODAY, and MONTH functions in the following formula:
=DATE(YEAR(TODAY()),MONTH(TODAY()),A2)
Thus, on entering \”5\”, the formula will automatically create a date representing the 5th of the current month in the current year.
Solution 2:
Enter the entire date, without delimiters, and use the DATEVALUE, [...]

Read Full Post »

Finding the Last Day of a Given Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+num_mths,0) where num_mths = 0 for the end of month for the current month and num_mths = 1 for the end of the month for the next month, etc.
1. Tried the formula if num_months = 0, it gives the end date for the prev

Read Full Post »

Selecting a random value from a List.

Using the RAND function in the following formula:
=INDEX(A2:A7,ROUND(RAND()*COUNTA(A2:A7),0))
The value returned by the formula will change with each recalculation (F9).

Read Full Post »