Intermediate level information
I recently exported an iPod playlist to check the total duration of time for all songs, but found the unit of time to be in seconds. For example, 274 seconds is not a useful time format for song duration. Converting this to a minutes:seconds format is my task. Getting minutes is easy, just divide 274 …
In previous posts I’ve been going through some formulas designed to extract and convert a text string Date, to a Date serial number. Extract a Date from Text in Excel The MID Function in Excel The RIGHT Function in Excel CONCATENATE Function or Ampersand Operator in Excel The TYPE Function in Excel The VALUE Function …
The VALUE Function formula in cell G2 has converted the text string Date from cell E2, into a date serial number. =VALUE(E2) Until recently, I always used the VALUE Function to do this conversion. It’s something that’s well documented in the Function Arguments. Excel Value Function Alternative Formulas to Convert Text Dates However, all that’s …
Excel is built to recognize date serial numbers like 40359 (Windows Date System) as Jun 30, 2010. No matter how this type of Date is formatted, the underlying value is numeric. Excel will recognize text as a Date, if its in a known date format. However there are limitations to Dates that are text strings. …
The CONCATENATE Function joins several text strings into one text string. You can also use the Ampersand (&) calculation operator instead of the CONCATENATE Function. The CONCATENATE Function has one required argument and up to 255 arguments, all separated by commas. The arguments can be text strings, numbers, or single-cell references. In my spreadsheet example, …
The data you see in column A is a text string output from a database. I need to extract the Date, and have used the MID Function to get the Month and Day, yet need to include the Year value as well. For this data the RIGHT Function is appropriate because the Year value is …
By looking at my exported data you can see the Dates in column A are really just a bunch of Text. The database export format spacing is standard: Day of the Week, space, Month, space, Day, space, Time, space, Year. I want to extract the Month and Day by using the MID Function. In cell …
Excel 2008 for Mac requires more work to remove duplicate values in a data set because of it’s lack of features. Excel 2007 and 2010 have the Remove Duplicates feature. Excel 2003 and Excel 2008 for Mac don’t. In my last post I used a formula and the Find box to remove duplicate values in …
I have a worksheet with duplicate values that need to be removed. There are two columns of data, with Date in column A and Weight in column B. The Date values are for each day and extend to the end of June. Some of the Weight values repeat as you can see in the picture. …