Multiple Date Formulas

[Tank2001]

Hello!

I'm trying to determine a working formula that will do the following

CellA4 will display total days from CellA1 to CellA3

If CellA3 is 0 then display total days from CellA1 to CellA2

Any help is help is appreciated!!!

 

[jproffer]

Are A1, A2, and A3 dates or a number of days or.....what?

If they're dates, then maybe this:

=IF(A3=0,A2-A1,A3-A1)

If they're numbers of days this:

=SUM(A1:A3) (it won't matter if A3 is empty or not)

 

[Tank2001]

Are A1, A2, and A3 dates or a number of days or.....what?

If they're dates, then maybe this:

=IF(A3=0,A2-A1,A3-A1)

If they're numbers of days this:

=SUM(A1:A3) (it won't matter if A3 is empty or not)

A1=Start Date
A2=Resolve Date
A3=Credit Passed Date
A4=Rolling total of days

When I attempted to use the formula provided it generated a fairly large negative number.

 

[jproffer]

When I put:
"2/10/18" in A1 formatted as a date
"2/15/18" in A2 formatted as a date
"2/25/18" in A3 formatted as a date

and:

"=IF(A3=0,A2-A1,A3-A1)" in A4 formatted as General.

It gives me a result of 15. If I clear A3, it changes to 5.


Not sure what's different in your workbook. It's hard for me to test it without being able to replicate the problem you're having.

 

[Tank2001]

When I put:
"2/10/18" in A1 formatted as a date
"2/15/18" in A2 formatted as a date
"2/25/18" in A3 formatted as a date

and:

"=IF(A3=0,A2-A1,A3-A1)" in A4 formatted as General.

It gives me a result of 15. If I clear A3, it changes to 5.


Not sure what's different in your workbook. It's hard for me to test it without being able to replicate the problem you're having.

Your formula worked actually. I was referencing to over a year's time so it came back with that larger final number. Thanks!!!!

 

[Tank2001]

Is there any way to alternatively have A4 show the total number of days to date if all of the other cells are blank?

 

[jproffer]

Assuming you mean all cells other than A1, then yessir .

=IF(AND(A3="",A2=""),ROUND(NOW()-A1,0),IF(A3="",A2-A1,A3-A1))

If you leave the Round out of it, like:

=IF(AND(A3="",A2=""),NOW()-A1,IF(A3="",A2-A1,A3-A1))

It will return the number of days, hours, minutes, seconds as a decimal. I.E. - 36.14278 days, so whichever you prefer.

 

[markmzz]

Hi!

Maybe the formula below can help you:

=IF(LOOKUP(7^6,A$1:A$3)=A$1,TODAY(),LOOKUP(7^6,A$1:A$3))-A$1

Markmzz