Ever had to figure out the total amounts received from all of your eBay sales in a year? Or when you should stock up your inventory for a list of product based on a 90-day cycle? Or simply converting an Excel date serial number to its appropriate date form? Here are a few useful formulas for the next time you encounter these and other similar challenges.
First of all, imagine that you have a list of dates that you want to copy from another document into an Excel spreadsheet. You do your usual right-click + copy (or Ctrl + C), shift over to Excel, and right-click + paste (or Ctrl + V); and behold...a list of serial numbers came into view.
But I copied a list of dates, not serial numbers. You thought to yourself. How do I fix this?
The answer to this is quite simple: Highlight the column where the data resides (i.e. Click on the column header "C" if the serial numbers are pasted into column C), go to the Home tab, then select "Short Date" in the Numbers section. This should change all of the serial numbers to a short date format (i.e. mm/dd/yyyy). If you would prefer another date format, you may select "Long Date" or a Custom format under "More Number Formats...".
Now there are times when Excel would not change the serial numbers to dates even after you've done the above. A work-around would be to first paste the dates into a text editor (such as Notepad) highlight the column in Excel where the data will be pasted into, format it to "Short Date", copy the data from Notepad, then paste it into Excel. Going through Notepad removes any special formatting that the date may have had.
Next, we will talk about finding the total amount of sales for a particular year. You may have sold 30 bags of chips, 15 iTunes gift cards, 45 boxes of Belgian chocolates, and a fish tank in three years. You may have had multiple orders on the same day, but they will most likely be scattered through the year. When you pull your sales history for the past three years, you will have a detailed list of what was sold on each day. But what if you just care about which year something was sold? How would you extract the year component from the dates? To test this, let's work on a mini exercise:
1. Open a new Excel workbook.
2. On Sheet1, enter "=TODAY()" in cell A1, without the double-quotes. This formula gives you today's date (in accordance to the system date).
3. Then in cell B1, enter "=YEAR(A1)" without the double-quotes. You should then see the year from the date in cell A1 displayed. Thus, the YEAR function extracts the year component from a given date. You can apply this knowledge to find the year of all of your eBay sales and get the sum of amounts via a PivotTable.
Finally, if you want to stock up your eBays inventory every 90 days, and all of your products have different initiation dates, how do you figure out the exact dates that you should stock up (as the months vary in the number of days, 90 days from 1/15 may not be 4/15)? Well, Excel actually has done all the hard work for you already. Let's use the date that we prepared in the previous example in cell A1. If we want to add another 90 days to today's date, all we need to do is enter "=A1+90" in another cell. The new cell would then display the date that is 90 days after today's date.
Hope you've found these information useful, and please reach out if there are questions!