1

I have 5 columns. I need to find the time between (in days) between values of the same type in the second column. The last column already calculates the difference in time between every date. It does this by taking the cell current cells date minus the previous cell's date. IE: it's been 1 day since the last event

I am trying to figure out how to do this: Column 3 would find the difference in time between Column 2 that contains "s". Column 4 would find the difference in time between all other letters.

Example data:

example data

  • What do you mean by "Column 4 would find the difference in time between all other letters"? Please update your question and the sample data to include the full expected output in columns 3 and 4. – Atzmon Nov 13 '16 at 11:04
0

First add a header row above your data, on row 1, and have your data starting on row 2.

In cell C2, enter this formula:

=IFERROR(IF(B2="s",A2-INDIRECT("A"&LOOKUP(2,1/($B$1:$B1="s"),ROW($B$1:$B1))),"-"),"n/a")

In cell D2, enter this formula:

=IFERROR(IF(B2<>"s",A2-INDIRECT("A"&LOOKUP(2,1/($B$1:$B1<>"s"),ROW($B$1:$B1))),"-"),"n/a")

Fill each formula down across all your rows. You'll get this result:

image of "S" and "non S" date calcs

Explanation

Column C formula works by first checking if column B = "s", then it searches (lookup) column B upwards for the previous instance of "s", and then uses the row number of that instance (indirect) to reference the date relating to it, and performs the date sum. The column D formula does the same except matches with anything other than "s".

The first instance of "s" in col C, and the first instance of "non-S" in col D, will result in errors (because no previous data) so the formulas are surrounded by iferror functions, which change the #N/A error result into a cleaner n/a string (cells C4 and D2).

If your data doesn't start at the top left

If your data does not start on row 2, still paste the above formulas in where I say and then copy and paste them to where they should be on your sheet, and most of the row references will update accordingly - you'll just need to change instances of $1: to match your header row (e.g. if your header row is 25 then instances of $1: would become $25:). If your data does not start in column A then you will need to manually change the column letters accordingly in the formulas. For example if your data starts in column F then change every instance of A to F, and every instance of B to G.

  • Thank you for your clear explanation and formula. This was exactly what I needed. – nightowl Nov 13 '16 at 19:04
0

I know this is not exactly how this should be done, but I usually try to use more columns to help me resolve such tasks. In one of them I have excel list the dates of the value I enter somewhere else (not sure what your task is, but for me it could be ID of the project). And then in additional column, it's easy to calculate difference between them. I know you could do script or something similar, but if you're OK with dirty solution, this one works too...

I would use another column with IF function, making it write the value of the date column if ID matches my required value. Such you'll get either empty cell or date. Then it should be easy to use another column calculating number of days since last non-empty cell.

Hope this helps...

  • Thank you for your suggestion. This is actually how I had it previously but I was working on this new method to refactor my old sheet. – nightowl Nov 13 '16 at 19:01

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.