Calculating # of days since last audit.

[stronik814]

I need to do the following but I'm a complete Excel novice when it comes to macros. I know the logic but don't know the syntax to write the macro. I was wondering if someone would be able to help me out.

-Thank you!


I'm trying to calculate Variable A for M1, the # of days since the last audit. Looking at the table below, from current date 11/7, the last audit was performed on 11/1. So 6 days since the last audit, so A = 6.

To do this, I was thinking of the following logic.

Search for today's date in column B.
Once found, set that variable to Q.
After, go over 2 columns right and select that value, let's say x.

If(x>=0, A = 0, else do the following: go up column D until x does not = blank.

Once x does not equal blank, go over 2 columns left and select that value.
Set that variable to W.

A = Q - W.

	A	B	C	D	E	F
1			Today's Date
2			11/7/14
3				0 = No Leak
4				1 = Leaks
5
6
7			# of days
8		M1	Variable A
9		M2	Variable B
10		M3	Variable C
11		M4	Variable D
12
13
14		Date		M1 Leak?
15		10/31/14		0
16		11/1/14		0
17		11/2/14
18		11/3/14
19		11/4/14
20		11/5/14
21		11/6/14
22		11/7/14

<TBODY>
</TBODY>

 

[Excel777]

Good question,me too waiting for the answer