Finding and recording a value from a previous day.

[eclairs]

Hello everybody

For the date (column b), I want to see if the value in (column c) appears one day earlier after 03:00 (columns b and a)

Eg - for 10/19/20 the value apple does appear on row 7 on 10/18/20 after 03:00

For the instances when this occurs can a word be recorded in the original row in (column d) as text saying “match” ?

Worksheet attached, thanks for your time.

 

[Hui]

Your Dates are text strings, not Dates

 

[eclairs]

Thanks Hui, the dates are now formatted as dates and not text strings. Submitted a fresh upload file.
Any ideas on a formula?

 

[vletm]

eclairs
... still Your 'dates' are not dates
... You can verify that ... write to cell F2 = B2+1
if Your cell B2 is date then it will show ... date.

 

[eclairs]

vletm reapplied the date formats and verified as you said. I think the date formatting is corrected now.
Resubmitting a fresh upload file.
Any ideas on a formula for the original question? Thanks for your ideas.

 

[Peter Bartholomew]

= IF(
   COUNTIFS(
      fruit, fruit,
      date,  date-1,
      time,  ">"&0.125 ),
   "Match", "" )

 

[GraH - Guido]

From the original file, Power Query alternative.

let
    Source = Excel.CurrentWorkbook(){[Name="tFruits"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"time", type time}, {"date", type text}, {"fruit", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"date", type date}}, "en-US"),
    AddColEarlierDate = Table.AddColumn(#"Changed Type with Locale", "1DayEarlierDate", each Date.AddDays([date],-1)),
    Join = Table.NestedJoin(AddColEarlierDate, {"1DayEarlierDate", "fruit"}, AddColEarlierDate, {"date", "fruit"}, "tFruits", JoinKind.LeftOuter),
    AddColMatch = Table.AddColumn(Join, "Match", each not List.IsEmpty(List.Select([tFruits][time], each _ > #time(3,0,0)))),
    #"Removed Columns" = Table.RemoveColumns(AddColMatch,{"1DayEarlierDate", "tFruits"})
in
    #"Removed Columns"

 

[vletm]

eclairs
It is not a format challenge - it's a value challenge!
I manually modified those to dates (E-column).
There are two samples without table and with table.

 

[eclairs]

Used Peter Bartholomew’s formula and it works. I will look into the Power Query alternative as I’ve never used it before.
Thanks to each of you for your time, awesome work!