Forum Discussion

Copper Contributor

Jul 26, 2020

Solved

Conditional formatting Date/Time in same cell

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 ti...

excel

Formulas and Functions

TheAntony
Jul 28, 2020
Flambert86 , replace the link in cell H2:
='1'!$A$17

with:
=DATEVALUE(SUBSTITUTE('1'!$A$17,",",""))

TheAntony

Iron Contributor

Jul 27, 2020

Flambert86 , try this in the conditional formatting:

=NOW()-$H2>=TIME(0,30,0)

Flambert86

Copper Contributor

Jul 27, 2020

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!

TheAntony
Iron Contributor
Jul 27, 2020
Flambert86 , can you share a sample of the workbook without any confidential info?
- Flambert86
  Copper Contributor
  Jul 27, 2020
  TheAntony
  
  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!
  - TheAntony
    Iron Contributor
    Jul 27, 2020
    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.