Aging formula with unknown dates
 

I have a problem I am hoping someone can help me with. Here is what I am
calculating:
A = Work Completion Date
B = Fixed Price Date
C = Receipt Date
D = Invoice Date
E = Difference between A + B
F = Difference between A + C
G = Difference between A + D
H = Difference between B + C
I = Difference between C + D

For E the formula I am using is: =IF(B1=0,TODAY()-$A$1,B1-$A$1)
For F the formual I am using is: =IF(C1=0,TODAY()-$A$1,C1-$A$1)
For G the formula I am using is: =IF(D1=0,TODAY()-$A$1,D1-$A$1)
For H the formula I am using is: =C1-B1
For I the formula I am using is: =D1-C1

Things work except when A has no date in it. What do I add to my formula in
E-G to result in 0 or leaving the cell blank when that purchase order work
has not been completed? The results of these calculations will be used in
pivot tables, charts, and conditional formatting so the answers of 39,679 or
so that I get when this is the case, plays havoc in them. This is a backwards
AR tracking that I am tracking due to a vendor not billing us for work
completed in the contracted time period and I need to know how many days have
elapsed between the 4 dates to determine whether the invoice can be paid, or
needs to be penalized according to the contract.

Thank you

Aging formula with unknown dates
 

Since the ball starts rolling with A1,
think you could try adding a simple IF check like this:
=IF($A$1="","",<yourformula)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Serendipity" wrote:
I have a problem I am hoping someone can help me with. Here is what I am
calculating:
A = Work Completion Date
B = Fixed Price Date
C = Receipt Date
D = Invoice Date
E = Difference between A + B
F = Difference between A + C
G = Difference between A + D
H = Difference between B + C
I = Difference between C + D

For E the formula I am using is: =IF(B1=0,TODAY()-$A$1,B1-$A$1)
For F the formual I am using is: =IF(C1=0,TODAY()-$A$1,C1-$A$1)
For G the formula I am using is: =IF(D1=0,TODAY()-$A$1,D1-$A$1)
For H the formula I am using is: =C1-B1
For I the formula I am using is: =D1-C1

Things work except when A has no date in it. What do I add to my formula in
E-G to result in 0 or leaving the cell blank when that purchase order work
has not been completed? The results of these calculations will be used in
pivot tables, charts, and conditional formatting so the answers of 39,679 or
so that I get when this is the case, plays havoc in them. This is a backwards
AR tracking that I am tracking due to a vendor not billing us for work
completed in the contracted time period and I need to know how many days have
elapsed between the 4 dates to determine whether the invoice can be paid, or
needs to be penalized according to the contract.

Thank you

Aging formula with unknown dates
 

I tried this with no luck.
=IF($T17="","",(V17=0,TODAY()-$T17,V17-$T17))
and
=IF($T17="","",V17=0,TODAY()-$T17,V17-$T17)

The formula that works except for the wrong answers is:

=IF(V17=0,TODAY()-$T17,V17-$T17)

V17 = no date
T17 = no date or with a date

and the answer I am getting is 39679 or in that neck of the woods.

What did I do wrong?

Thanks,

"Max" wrote:

Since the ball starts rolling with A1,
think you could try adding a simple IF check like this:
=IF($A$1="","",<yourformula)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Serendipity" wrote:
I have a problem I am hoping someone can help me with. Here is what I am
calculating:
A = Work Completion Date
B = Fixed Price Date
C = Receipt Date
D = Invoice Date
E = Difference between A + B
F = Difference between A + C
G = Difference between A + D
H = Difference between B + C
I = Difference between C + D

For E the formula I am using is: =IF(B1=0,TODAY()-$A$1,B1-$A$1)
For F the formual I am using is: =IF(C1=0,TODAY()-$A$1,C1-$A$1)
For G the formula I am using is: =IF(D1=0,TODAY()-$A$1,D1-$A$1)
For H the formula I am using is: =C1-B1
For I the formula I am using is: =D1-C1

Things work except when A has no date in it. What do I add to my formula in
E-G to result in 0 or leaving the cell blank when that purchase order work
has not been completed? The results of these calculations will be used in
pivot tables, charts, and conditional formatting so the answers of 39,679 or
so that I get when this is the case, plays havoc in them. This is a backwards
AR tracking that I am tracking due to a vendor not billing us for work
completed in the contracted time period and I need to know how many days have
elapsed between the 4 dates to determine whether the invoice can be paid, or
needs to be penalized according to the contract.

Thank you

Aging formula with unknown dates
 

Let's take your original line, as an example:
For E the formula I am using is: =IF(B1=0,TODAY()-$A$1,B1-$A$1)

What I meant by the indicative:
=IF($A$1="","",<yourformula)

was to apply it like this:
=IF($A$1="","",IF(B1=0,TODAY()-$A$1,B1-$A$1))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Serendipity" wrote:
I tried this with no luck.
=IF($T17="","",(V17=0,TODAY()-$T17,V17-$T17))
and
=IF($T17="","",V17=0,TODAY()-$T17,V17-$T17)

The formula that works except for the wrong answers is:

=IF(V17=0,TODAY()-$T17,V17-$T17)

V17 = no date
T17 = no date or with a date

and the answer I am getting is 39679 or in that neck of the woods.

What did I do wrong?

Thanks

Aging formula with unknown dates
 

THANK YOU!!!!! You are awesome....
I took the = and if out instead of just the =.
Thank you again and again... you just saved me a lot of time!

Serendipity

"Max" wrote:

Let's take your original line, as an example:
For E the formula I am using is: =IF(B1=0,TODAY()-$A$1,B1-$A$1)

What I meant by the indicative:
=IF($A$1="","",<yourformula)

was to apply it like this:
=IF($A$1="","",IF(B1=0,TODAY()-$A$1,B1-$A$1))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Serendipity" wrote:
I tried this with no luck.
=IF($T17="","",(V17=0,TODAY()-$T17,V17-$T17))
and
=IF($T17="","",V17=0,TODAY()-$T17,V17-$T17)

The formula that works except for the wrong answers is:

=IF(V17=0,TODAY()-$T17,V17-$T17)

V17 = no date
T17 = no date or with a date

and the answer I am getting is 39679 or in that neck of the woods.

What did I do wrong?

Thanks

Aging formula with unknown dates
 

Welcome, glad you got it going.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400, Files:358, Subscribers:55
xdemechanik
---
"Serendipity" wrote in message
...
THANK YOU!!!!! You are awesome....
I took the = and if out instead of just the =.
Thank you again and again... you just saved me a lot of time!