Using Conditional Formatting/ Formula to Compare Two Dates

Question

I am trying to compare two dates in a large data sheet as a Success or Failure depending on the Ship Date. For example, on the first line, the Ship Date is 2/29/2024 which is after the due date (-1 of 2/26/2024), making it a failure. The Expected Delivery Date -1 > Ship Date.

Answer 1

Hello @Villa, Carissa,

welcome to this moderated Azure community forum.

Better questions with more information lead to better answers from our community members.

Please provide information about the steps you have taken, the guide you follow, and the errors you receive.

Where do you want to compare the dates, in Azure Data Explorer? Have you imported this datasheet in Azure Data Explorer already?

If so, you will have a table like:

.create table shippingtest
(
    item: string,
    ship_date : datetime,
    expected_delivery_date : datetime  
)

.create table shippingtest ingestion json mapping "JsonMapping"
'['
'    { "column" : "item", "datatype" : "string", "Properties":{"Path":"$.item"}},'
'    { "column" : "ship_date", "datatype" : "datetime", "Properties":{"Path":"$.ship_date"}},'
'    { "column" : "expected_delivery_date", "datatype" : "datetime", "Properties":{"Path":"$.expected_delivery_date"}}'
']'

.ingest inline into table shippingtest with (format = "json", ingestionMappingReference = "JsonMapping") <|
{"item": "item1", "ship_date": "2024-02-17", "expected_delivery_date": "2024-02-20" }
{"item": "item2", "ship_date": "2024-02-18", "expected_delivery_date": "2024-02-20" }
{"item": "item3", "ship_date": "2024-02-19", "expected_delivery_date": "2024-02-20" }
{"item": "item4", "ship_date": "2024-02-20", "expected_delivery_date": "2024-02-20" }
{"item": "item5", "ship_date": "2024-02-29", "expected_delivery_date": "2024-02-26" }

As you can see, I already added a few rows.

You can query the data for success or failure like this:

shippingtest
| extend shippingSuccess = ship_date < datetime_add('day', -1, expected_delivery_date)
| order by ship_date asc

Here I extend the table with an extra (Boolean) column but the equation could have been a 'where clause' too.

Notice how 1 day is subtracted using "datetime_add('day', -1, expected_delivery_date)".

The result looks like this:

So, only the shippings of the first two rows are marked successful.

---

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.

Answer 2

Hello @Villa, Carissa

To compare two dates in Excel, you can use the IF function along with the DATEDIF function.

Here's an example formula that you can use:

=IF(DATEDIF(B2,C2,"d")>=0,"Success","Failure")

In this formula, B2 is the cell containing the Due Date, C2 is the cell containing the Ship Date, and "d" is the unit of time that you want to use for the calculation (in this case, days).

The DATEDIF function calculates the difference between the two dates, and the IF function checks whether the result is greater than or equal to zero.

If it is, the formula returns "Success"; otherwise, it returns "Failure".

You can copy this formula down to the other rows in your data sheet to apply it to all of the rows.

I hope this helps further in your Excel journey.