Records Max 0:00
640 Min 0:00:00
Place Date/Time
1 M1-T-Rifle - A 1 09/08/2016 08:10
2 M1-T-Rifle - A 2 09/08/2016 08:27
3 M1-T-Rifle - A 3 09/08/2016 07:58
This is generated in 8.csv format. I then save it in 2016 Office Excel.
I have 640 rows of data. I need to find the oldest TIME or MAX. And the youngest TIME or MIN. And subtract one from the other.
I can't get any formatting to work. Can you help. Thanks
Use a helper column to get the date/time using:
B1:Get max in B1: =MAX(B:B)
Please Login or Register to view this content.
min in C: =MIN(B:B)
then substract max - min
Ben Van Johnson
Place Date/Time
09/08/2016 08:10
09/08/2016 08:27
09/08/2016 07:58
This shows better the data in the column.
I have 640 rows like this. So I put in a cell a formula "=MAX(B1:B640)"
"=MIN(B1:B640)"
Then a formula subtracting MIN from MAX to find the time lapse.
Doesn't work.
To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I must be missing something simple.
Look like column B is imported from csv, displayed as date/time format. Try to add 0 to each cell to convert into real value by:
Copy any blank cell (point to any blank cell, Ctrl-C)
Choose B column (click on the column header to choose)
Paste special/Operation/Add
OK
Quang PT
Could I suggest a simpler idea, particularly as you are imported data from a .csv file. All number calculations / data extracts will be in text, so you need to convert your data to an Excel Date number. I do this on a regular basis. Try inserting another column and add the following simple DATE Function with a TIME added on the end, i.e. if your first data value is in cell A2, try inserting in C2 =DATE(MID(A2,26,4),MID(A2,20,2),MID(A2,23,2))+RIGHT(A2,5). The syntax for the DATE Function is DATE(Year,Month,Day), so I assumed you had your data set in American "mm/dd/yyyy" date format rather than UK "dd/mm/yyyy". Hence, the DATE Function will extract a number, and the last element will add the decimal time value to the day value. Hey presto, you now have a number format that will allow you to conduct time calculations like MAX / MIN. Hope that helps, it works a dream for me. If in doubt, experiment with purely the MID Function for each value to prove that you can extract a TEXT value and convert it to a time function.
I suggest two ways to solve.
The Aggregate worked. The other returned a "#VALUE!
I wonder if my Excel needs to be reinstalled. That being said I'm on Office 365. So it should be up to snuff. I got my original formulas to work on one download. But not on any others. So something went sideways in my formatting. I think. Thank you!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks