0

I have a set of data from a program that I export; and it varies in range. Sometimes there will be 9 cells in a column and sometimes, like, 40, for example. The very first cell in the column, when exported to Excel, is text that I don't need and the columns after the final populated cell are blank cells that I don't need. How can I figure out the time difference between the last populated cell and the first one?

When exported, the data in each cell (that isn't the first cell or a blank cell) looks like this: 4/25/2018 12:10:00 AM. Assuming column "B" is what I used, I tried the following formula:

=IF(B:B(ISNUMBER), (TEXT(MAX(B:B)-MIN(B:B), "hr:mm:ss")), "")

which (in my eyes) means "If the cells in B have a number, the difference between the max and min (latest and earliest) times will be found in hours:minutes:seconds, and the formula cell will be blank otherwise."

What am I doing wrong?

2

The biggest issue is that MAX() and MIN() will ignore text values and blanks, so all you need is:

=MAX(B:B)-MIN(B:B)

To format the result, just choose Format > Cells... from the menu and pick one of the Categories offered.

If you format the cell holding the result as a number, what you'll see is a decimal number giving the difference in days. If you give it a custom format of [h]:mm:ss, it will display the result in hours, min, and secs, even if the hours are greater than 24.

If you specify a format of hh:mm:ss, it will display the hours as less than 24 and not show any of the days that were (or might have been) there. There is no hour format of "hr".

See this reference on how to use ISNUMBER(). If you had written it correctly as ISNUMBER(B:B), it would return an array of True/False values for the entire column.

I hope this helps, and good luck.

| improve this answer | |
  • Hiya, @fix. I saw your msg in the chat and answered. – Bandersnatch May 7 '18 at 1:27
-1

There is nothing wrong with your formula. But a simple modification in the formula is required to execute it properly.

You need to be sure that,

  1. The data range where you have imported Date & Time must be formatted as "dd/mm/yyyy hh:mm:ss Am/PM" or "mm/dd/yyyy hh:mm:ss".

  2. The cell where you are calculating the time difference has to be formatted as General.

Please see the screenshot below:

enter image description here

The formula in cell D57 is =IF(ISNUMBER(C57:C62),TEXT(MAX(C57:C62)-MIN(C57:C62),"HH:mm:ss"),"")

Note: Please adjust the data range as necessary.

| improve this answer | |

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.