Formula Errors

How to Calculate Days Between Dates Based on Location in Excel

Maira Mehtab Sep 27, 2026 870 views

Question details

The user needs a formula to calculate the number of days between a sample date and a result date, where the specific result date column to use depends on a location value.

Product: Excel
Device & OS: not provided
Scenario: Calculating turnaround times or duration between dates conditionally based on text criteria in another column.
Observed behavior: The calculation needs to dynamically select the correct date column to subtract from based on the location specified.

Before you start

Ensure your date columns are properly formatted as Dates in Excel, and verify the exact spelling of the location values used in your dataset to avoid formula errors.

Solution 1Recommended

Use the IF Function for Conditional Date Calculation

Use a standard IF formula to check the location column and calculate the date difference using the appropriate result date column.

The IF function allows you to perform a logical test. By checking the location value, Excel can determine which date column should be subtracted from the original sample date.

Select the target cell for the first location

Click on the cell where you want the result for your first location (e.g., cell E2 for a location named 'hospi').

Enter the first condition formula

Type =IF(B2="hospi",C2-A2,"") where B2 is the cell containing the location, C2 is the result date for 'hospi', and A2 is the sample date.

Enter the second condition formula

In the next column (e.g., cell F2 for a location named 'labo'), enter =IF(B2="labo",D2-A2,"") where D2 is the alternative result date.

Fill the formulas down

Select both formula cells and drag the fill handle (the small square at the bottom-right corner) down to apply the calculations to the rest of your rows.

Customize the Formula: Replace 'hospi' and 'labo' with the exact text strings used in your worksheet. Make sure your date cells contain actual date values, not text.

Calculate Dates Easily with WPS

Calculate Conditional Date Differences in WPS Office

WPS Spreadsheet fully supports Excel functions like IF, DATEDIF, and standard date subtraction. You can seamlessly calculate durations and manage complex datasets for free.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the dates and locations.
2. Apply the IF formula: Select an empty cell and enter your conditional formula, such as =IF(B2="location",C2-A2,"").
3. Format as numbers: If the result shows as a date instead of days, right-click the cell, select 'Format Cells', and change the category to 'Number' or 'General'.
4. Apply to all rows: Double-click the small square at the bottom-right of the cell to fill the formula down.

100% compatible with Microsoft Excel formulas like IF and date calculationsLightweight application that runs smoothly on Windows, Mac, and LinuxAdvanced date formatting and data validation toolsFree to use with a familiar, easy-to-learn interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my date subtraction returning a weird date instead of a number?

Excel sometimes automatically formats the result of a date calculation as a Date. To fix this, select the result cells, right-click, choose 'Format Cells', and change the number format to 'General' or 'Number'.

How can I calculate days without counting weekends?

You can use the NETWORKDAYS function. For a conditional calculation, combine it with the IF function like this: =IF(B2="hospi", NETWORKDAYS(A2, C2), "").

Can I combine the results for both locations into a single column?

Yes, you can use a nested IF statement to keep everything in one column. For example: =IF(B2="hospi", C2-A2, IF(B2="labo", D2-A2, "")) will output the correct days based on whichever location is present.

Excel & Data Problems