Forum Discussion

Excellent's avatar
Excellent
Copper Contributor
Nov 09, 2026

How to change the columns a formula will get its data from

Here is a dummy example of what my spreadsheet looks like, the end date/time can be in different columns based on external factors. 

 abcd
1Start date/timeOther date/timeEnd date/timeAmount of time
2January 1 2026 12:41:55 PM January 1 2026 12:50:00 PM 
3January 1 2026 3:30:50 PMJanuary 2 2026 5:00:00 PM  
4January 2 2026 4:00:55 PMJanuary 2 2026 10:00:00 PM  
5January 13 2026 12:30:00 PM January 13 2026 12:33:00 PM 
     

How do I calculate the difference in time in column D based when the end date and time may be in one of two columns?

Before I would just subtract one column from another and then format the cells to create a readable output automatically, but I need a formula to take into account two columns and to choose the one that is not empty.

I am unsure how to write the in then else in a way that Excel can understand. 

What I have tried:

= IF c2 = " " THEN b2-a2 ELSE c2-a2

BI & Data Analysis
excel
Excel for web
Excel on Mac
Excel on mobile
Formulas and Functions
office 365

3 Replies

  • Harun24HR's avatar
    Harun24HR
    Silver Contributor
    Nov 09, 2026

    There are few ways to achieve this. Here are few-

    =XLOOKUP(1,B2:C2,B2:C2,,1,-1)-A2
=TAKE(FILTER(B2:C2,B2:C2<>""),,-1)-A2
=INDEX(A2:C2,1,MAX((B2:C2<>"")*COLUMN(B2:C2)))-A2
    Time difference based on last row.

     

  • Jason_M's avatar
    Jason_M
    Iron Contributor
    Nov 09, 2026

    Enter this formula in D2 and fill down:

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

    If C2 is empty, it subtracts A2 from B2. Otherwise, it subtracts A2 from C2. Use two quotation marks with nothing between them to test for an empty cell.

    Select column D, press Ctrl+1, choose Custom, and enter:

    [h]:mm:ss

    The brackets allow elapsed hours to exceed a full day.

    If both end-time cells may be empty, use:

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

    The dates must be recognised by Excel as date/time values, not text. If both B and C contain a date, the formula uses C.

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Nov 09, 2026

    So you have a couple options.  IF there is only 1 time between the 2 columns (i.e. time will be in EITHER column B OR column C and never BOTH) then you can simple add those 2 columns and subtract column A:

    =C3:C6+B3:B6-A3:A6

    Note the above is an array formula for the entire columns, if you aren't familiar with that you only need to paste this in the first cell (D3) and it will 'SPILL' down the results to all the corresponding cells

     

    On the other hand IF there could be time in both then you need to determine the order of precedence in the sense of which to use in that case.  Let's assume you prefer 'End date/time' (i.e. column C) in that case then you want to use column B if column C is "" otherwise use column C:

    =IF(C3:C6="",B3:B6,C3:C6)-A3:A6

    So if the value in column C is blank the IF will return column B, otherwise that IF will return column C and then it will subtract column A from whichever value is returned.

     

    NOTE if both columns are blank but there is a date/value in column A both of the above will resullt in an ERROR on that line due to a 'negative' date/time.  You can fix that using custom format:

    so in columns D & E are the 2 versions I gave above and then I applied a custom number/time format to show hours:min:sec but added a semicolon after that format to say if negative show nothing.  BTW the brackets around the hours indicate it can/should show the total hours and go over 24 hours and NOT show only the hour portion of the Days, Hours, Mins, Seconds.