Forum Discussion

Mark-AGI's avatar
Mark-AGI
Copper Contributor
Oct 18, 2022

Excel time formula

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.

Formulas and Functions

3 Replies

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Oct 18, 2022

    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)

     

    • SnowMan55's avatar
      SnowMan55
      Bronze Contributor
      Oct 18, 2022
      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.)
      • dscheikey's avatar
        dscheikey
        Bronze Contributor
        Oct 18, 2022

        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.

Resources