0

I have a problem that I have been unable to solve, or find a result that works for me.

I have a sheet with data entered from a lab. The dates are based on sampling time, and each sample point is sampled on different frequencies. Ex:

Sample A 1/1/2014 13:03 (Different column, "/c" in future) 6.2 /C Sample B 1/1/2014 16:43 /C 7.2 (and so on for about 50 samples)

I will have hundreds of these results in a sheet, and I need to make a summary that can lookup for a month and year I enter, and return the average of all those results for that entered month. I have been successful doing this when the time is not a factor, using lookup and match functions, but unsuccessful when the time changes.

I've tried using Month functions, but haven't figured out how to extract the dates that match the month.

Any ideas? I have a decent Excel knowledge but this one is stumping me.

2
  • 1
    You might want to tag Excel or at least mention it higher up in your question. I wasn't 100% what you were talking about until the very last sentence.
    – Steve
    Oct 24, 2014 at 15:45
  • 1
    It's hard to visualize what the data looks like as written. Could you mock up something that shows individual column headings and a row of data?
    – fixer1234
    Oct 24, 2014 at 15:54

2 Answers 2

0

Just some suggestions: if you can, separate the sample name and its datatiem into two columns (or three columns: sample, date, time), then use the sort/filter function provided by Excel.

Another idea is add new columns, and write a simple function to extract the date string from the cell containing sampling time, then sort/filter by the column(s) containing these functions.

sample function: date '=left( right(A1, len('1/1/2014 10:30'), len('1/1/2014'))'

better idea is use search function to get the start and end position of the substring (only containing date) in the sampling time string, then extract month and/or day.

in some cases, trim the whitespace at the beginning and at the tail of the sampling time string is necessary.

some Excel string function references:

http://office.microsoft.com/en-us/excel-help/text-functions-reference-HP010079191.aspx http://office.microsoft.com/en-us/excel-help/trim-function-HP010062581.aspx?CTT=5&origin=HP010079191 http://office.microsoft.com/en-us/excel-help/search-searchb-functions-HP010062577.aspx?CTT=5&origin=HP010079191

Better practice: contain the lab so that they can provide you the data in a better format (have sample name, date, time separated).

0

I agree the date and sample ID should be stored in a seperate column but if you just need to extract the month you could use the below

Cell Value Sample B 1/1/2014 16:43 (assume this is mm/dd/yyyy)

=MID(A1,FIND("/",A1)-2,2)*1

Result is 1.

How this works.

This finds the first / in the cell and returns the 2 digits in front of that.

The * 1 converts the text to a number and will also strip the leading 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.