A date is within a specified time period

1.1 Expected Effect

Users sometimes need to determine whether a time field is within a specified time period, for example, whether "Invoice Time" is between "Account Entry Time" and "Shipment Time", as shown in the following figure:

1.2 Implementation Ideas

Use the statement IF, AND and DATEDIF.

Calculate the time difference between "Shipment Time" and "Account Entry Time", "Invoice time" and "Account Entry Time", "Shipment Time" and "Invoice Time" respectively.

Using if judgment, if the time difference between "Shipment Time" and "Account Entry Time" is greater than the other two time differences at the same time, the "Invoice Time" is between "Account Entry Time" and "Shipment Time".

Example data:Time-Judgment.xlsx

Upload this Excel dataset to FineBI, as shown in the following figure:

2.1 Create a Dataset

Add a dataset, and select an Excel dataset, and check all fields, as shown in the following image:

2.2 Judge the Time

Click on the button to add "New column", as shown in the following figure:

Name the new column as "Judging Results", enter the formula:

IF(AND(DATEDIF(${Account Entry Time},${Shipment Time},"D")>(DATEDIF(${Invoice Time},${Shipment Time},"D")),DATEDIF(${Account Entry Time},${Shipment Time},"D")>DATEDIF(${Invoice Time},${Account Entry Time},"D")),"Yes","No")

then click OK, as shown in the following figure:

Note: the functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

Formula explanation:

2.3 View the Effect

See Section 1.1 of this document for details.