Excel time formula

[Mark-AGI]

Afternoon,

I'm trying to get cells in column E to perform a function and calculation based on particular text in column A.

My objective is to calculate the time taken (between two dates Column B and C) but for "completed" (Column A) tasks only. However, if Column A reads “Pending” then I want to display 0 (zero) in column E)

	A	B	C	D	E
1		Date Entered	Complete date
2	Complete	01/02/2022	05/06/2022		131
3	Pending	01/02/2022	-		0

Any support / suggestion would be amazing.

[#2]

@Mark-AGI 

Hi Mark, your task can easily be done with IF(). Since dates in Excel are actually formatted numbers, you can simply subtract the two dates from each other.

Enter the following formula in E2. The result will automatically continue downwards.

=IF(A2:A3="Complete",C2-B2,0)

[#3]

I'm curious as to why your IF function's first argument refers to A2:A3, instead of just A2. (Odd things can happen when cells around such a formula are populated in an unexpected order.)

[#4]

@SnowMan55 

You're right! A clerical error on my part.

=IF(A2:A3="Complete",C2:C3-B2:B3,0)

 If you only

=IF(A2="Complete",C2-B2,0)

you have to copy the formula down.