Advanced Excel Date Editing and Formatting

Suppose you have an Excel file with dates in the format DD/MM/YYYY.

So you have entries like 02/03/1999 (2 March 1999). Suppose you want to change it to 2/3/1999, i.e. remove leading zeroes from one digit days and months.

It sounds easy, but it is actually quite tricky to do it in Excel without knowledge of the correct method. The normal methods of date formatting will simply not work.

After some searching, I found and tested that the following method is probably the fastest and easiest way to do it:

Select the date column and then click Data > Text to Columns Next, Next, then select Date ‘MDY’

then you should be able to do this using a number format of m/dd/yyyy or m/d/yyyy if you don’t want a leading zero in the days as well

Source: StackOverflow

Excel: Find Sum, Average, Median by ignoring NA or invalid values

Today I just found out about the AGGREGATE function in Microsoft Excel, which allows you to find the sum, average, median, product and various other statistical numbers while ignoring NaN values.

The usual Excel function of AVERAGE will produce errors if any of the cells are invalid.

With AGGREGATE, for instance if we want to calculate the average (while ignoring invalid cells), we just type in the Excel formula

=AGGREGATE(1,6, [data])