Jul 26 2020 04:36 PM - edited Jul 28 2020 02:57 PM
Hi all,
I have a sheet that pulls timestamp data from multiple sheets. I want to use conditional formatting to tell me if the timestamp is older than 30 minutes. The timestamp contains date and time information in the same cell with a format of "m/d/yyyy, h:mm:ss AM/PM" .
I cannot figure out what formula to use to find dates/times older than 30 minutes. Currently the sheet uses conditional formatting for unique entries but it has some errors.
I have attached a photo of the summary data. This is what it should look like when properly formatted.
Thanks for any help you can provide!
Jul 26 2020 05:32 PM
Jul 26 2020 06:05 PM - edited Jul 28 2020 02:57 PM
Thank you for the quick response. Unfortunately it did not work. Any other thoughts? I attached a new photo of the formula you suggested.
Thanks!
Jul 26 2020 06:08 PM
@Flambert86 , can you share a sample of the workbook without any confidential info?
Jul 26 2020 07:31 PM - edited Jul 28 2020 02:56 PM
Hi,
I have attached a copy of the summary sheet and 4 of the sheets that the summary draws data from. Thank you very much for your help!
Jul 26 2020 08:47 PM
@Flambert86 , The dates are not Excel dates, but rather text values. So the conditional format was failing. I noticed that the dates in sheets 1, 2, 3, and 4 are text values like this : "7/26/2020, 4:04:19 PM". Removing the comma between the date and time made Excel recognize them as dates. Perform a find and replace for ", " with " " and that should fix the issue. The conditional format will work after that.
Jul 27 2020 02:41 AM
Jul 27 2020 06:47 PM
SolutionJul 27 2020 06:47 PM
Solution