Forum Discussion
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. a b c d 1 Start date/time Other date/time End date...
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.