Aging with if statement

[dmxcasper2]

Problem Statement:

For Row 2 I'm using =today()-A1
For Row 3 I need a a formula that perform the following logic statement under these conditions:

• If Column B is blank then calculate using =today()-A1
• If Column B has a date then calculate Column A - Column B
• If Column A has a non date value like "TBA", then Column C should remain blank

Is it possible to keep Column values a positive integers.

​

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Column A[/TD]
[TD="align: center"]Column B[/TD]
[TD="align: center"]Column C - Aging[/TD]
[/TR]
[TR]
[TD="align: center"]11/05/2018[/TD]
[TD="align: center"]12/01/2018[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]11/05/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"]30[/TD]
[/TR]
</tbody>[/TABLE]

​

 

[gaz_chops]

Hi,

Do you mean "Row" or "Column"?

For "Row" 2
For "Row" 3

 

[dmxcasper2]

The formula would be in Column. Example shown below: C2, C3 and on.

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Column A[/TD]
[TD="align: center"]Column B[/TD]
[TD="align: center"]Column C - Aging[/TD]
[/TR]
[TR]
[TD="align: center"]11/05/2018[/TD]
[TD="align: center"]12/01/2018[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]11/05/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"]30[/TD]
[/TR]
</tbody>[/TABLE]

 

[Finalfight40]

Maybe:

=IF(ISTEXT(A1),"",IF(ISBLANK(B1),TODAY()-A1,B1-A1))

 

[gaz_chops]

or

=IF(OR(A2="",ISTEXT(A2)),"",IF(ISNUMBER(B2),A2-B2,TODAY()-A2))

 

[Finalfight40]

or

=IF(OR(A2="",ISTEXT(A2)),"",IF(ISNUMBER(B2),A2-B2,TODAY()-A2))

Good point i should have included where there is no data in the A column if they plan to drag this down further than the data.

=IF(OR(ISTEXT(A1), A1=""),"",IF(ISBLANK(B1),TODAY()-A1,B1-A1))

 

[dmxcasper2]

Thanks gaz and final.

I tried both formulas and both of them work as intended!

 

[jtakw]

Hi,

This would also work:

Book1
	A	B	C
1	11/5/2018	12/1/2018	26
2	11/5/2018		30
3
4	TBA
Sheet378

Cell Formulas
Range	Formula
C1	=IF(N(A1),IF(B1,B1,TODAY())-A1,"")

Formula copied down.