CODEWITHSUNDEEP
excelvba
user3634037
user3634037

Reputation: 13

Calculating days since an event in Excel

I am working with a weather data set. I am particularly interested in two columns which are cumulative precipitation and a date. My question is a simple one, though I am struggling to figure out the solution. Essentially I am wanting to determine days since precipitation. An example of the data is as follows:

WEATHER DATA

Pr  Date
40  8/8/2013
40  8/8/2013
40  8/9/2013
40  8/9/2013
41  8/10/2013
41  8/10/2013

In this example, if I know the last day it rained was 8/7, then 8/8 would have a value of 1 (days since precipitation), 8/9 would be 2, and 8/10 would go back to 0. I have multiple dates because of hourly recordings (I trimmed it down for this post). I've been trying to figure it out with conditional if|then statements, but I'm thinking VBA may be more appropriate here. Any help or insight would be greatly appreciated.

Upvotes: 1

Views: 409

Answers (1)

Andre Silva
Andre Silva

Reputation: 4928

Assuming cell C2 to be equal 1 (or start where you wish by adjusting the C2 value), the formula below works in the example you provided. Type in C3:

=IF(A3<>A2,0,IF(B3=B2,C2,1+C2))

Drag the formula down. Explanation:

If precipitation from time i+next is different from i it comes back to zero --> there was rain.

If time i+next is equal i, then it compares the date d+next with d.

  1. If they are equal hold the number of days without rain from previous cell.
  2. If they are not, add 1 day* to the value inside previous cell.

*I'm assuming you have consecutive days from the following sentence:

I have multiple dates because of hourly recordings

Upvotes: 1

Related Questions