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...
Formulas and Functions
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