Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have one column that has either nothing or a date, I want to calculate the days that past between the date just being entered and the previous date. My problem is, the gaps between dates always vary and this sheet will have hundreds or rows. any sugestions will help, i don't think writing an if statment to cover a span of 100 rows is practical or even if you can

share|improve this question

1 Answer 1

Is there a date in the first row of the column?

If so, then I would cheat - assuming you cannot just populate the blank fields with the preceding date, you could make a new hidden column that did. Lets assume A1 is the first date column. then B1, the hidden column, can contain =A1

B2 can contain

=if(A2,A2,B1)

So B2 contains A2 unless A2 is empty, then it contains B1. Paste this down the entire column and it will ensure there are no blanks.

Column C can then contain the difference calculation. It would contain the following pasted the entire length:

=if(B2<>B1,B2-B1,'')

Ie, show the number of days difference between the two dates, provided they are different, otherwise show a blank.

Example

share|improve this answer
    
OK, I tried it out and I think it is going to work out great but all the cells that have dates and don't have dates are populated due to the if(), =IF(S$1=$C2,$A2,""). So when i create my hidden column I get either the date or a #value error –  Bill Oct 5 '11 at 23:45
    
OK, I tried it out and I think it is going to work out great but all the cells that have dates and don't have dates are populated due to the if(), =IF(S$1=$C2,$A2,""). So when i create my hidden column I get either the date or a #value error, is this due to the logic false part being ""? I do not know how to force the if statement return a vaule to a diffrent column on the same row that way keeping rows without dates blank? –  Bill Oct 5 '11 at 23:51
    
I have added an example spreadsheet download in the original answer. A is the dates column, B is the column with the dates populated and C is the days calculation –  Paul Oct 6 '11 at 0:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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