How do I subtract dates using one formula if a cell has a date in it but another formula if it is blank

[jangell]

A1 has 7/22/17 18:50:00 in it
A2 has a 7/28/17 22:01:22 in it
A3 is where I want results

I can use a simple text formula to get the diff here =TEXT(a1-a2," d:h:mm") to get the elapsed days hours and minutes.

My problem is when there is no information in A2 (because it is not complete or I do not have a projected completion date) i would like it the formula to use the current date/Time =now() as a replacement for a blank B2 (this formula would sit in E1 to be used as needed) - this would give me an at a glance elapsed days hours minutes result in A3.

I tried =if(sum(a2<>,text(a1-a3),text(a1-a2) and a variety of other guesses but cant get it figured out...

Would appreciate help on this.

Thanks

 

[Aladin Akyurek]

Is this what you are looking for?

=TEXT(a1-if(isnumber(a2),a2,today())," d:h:mm")

where a1 <= a2 or a1 <= today's date.

 

[jangell]

That does not seem to work - It produces a #value error

Is there a way to load a spreadsheet sample up on the board?

 

[jangell]

Here is a copy of the spreadsheet - Hopefully i did this correctly.

Looking for a formula in the yellow highlighted cell

Sign in to your account

 

[AhoyNC]

Couldn't open your file as it needed a password.
But see if this works:

Excel Workbook
	A
1	7/22/2017 18:50
2
3	4:1:35
Sheet

 

[jangell]

I was unable to get that to work.

Here is a non password folder

Dropbox - Test 1.xlsx

 

[AhoyNC]

You need to change the cell references to match your data.

Excel Workbook
	A	B	C	D	E	F
1	Name	Bew Date	Ferment Type (Standard or Fast)	Ferment Start	Ferment Stop	Elapsed Frerment Time
2	Kayaker Cream Ale	7/17/17 20:50	Standard	7/17/17 22:00	7/22/2017 8:13	4:10:13
3	Mild Brown	7/17/17 22:00	Standard	7/19/17 22:00		6:23:35
Sheet

 

[jangell]

DOOOOOH.

Thanks for the help AhoyNC. Appreciate the assistance.