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

Formulas and Functions

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

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

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!

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.
  - Flambert86
    Copper Contributor
    Jul 27, 2020
    You are correct. They are text that is being pasted into the numbered sheets from a data source. The summary page pulls that information from the other sheets so each cell in the summary is actually a link to another cell in a seperate sheet.
    
    In order for this to work the way it needs to, finding and replacing the comma each time is not feasable.
    
    Is there another way to get it to recognize this as a date/time format?

Resources