SOLVED

Conditional formatting Date/Time in same cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1546914%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20Date%2FTime%20in%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546914%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740437%22%20target%3D%22_blank%22%3E%40Flambert86%3C%2FA%3E%26nbsp%3B%2C%20try%20this%20in%20the%20conditional%20formatting%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DNOW()-%24H2%26gt%3B%3DTIME(0%2C30%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TheAntony_0-1595809940840.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208012iEA89FC93841191D6%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TheAntony_0-1595809940840.png%22%20alt%3D%22TheAntony_0-1595809940840.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546935%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20Date%2FTime%20in%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740437%22%20target%3D%22_blank%22%3E%40Flambert86%3C%2FA%3E%26nbsp%3B%2C%20can%20you%20share%20a%20sample%20of%20the%20workbook%20without%20any%20confidential%20info%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1547006%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20Date%2FTime%20in%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740437%22%20target%3D%22_blank%22%3E%40Flambert86%3C%2FA%3E%26nbsp%3B%2C%20The%20dates%20are%20not%20Excel%20dates%2C%20but%20rather%20text%20values.%20So%20the%20conditional%20format%20was%20failing.%20I%20noticed%20that%20the%20dates%20in%20sheets%201%2C%202%2C%203%2C%20and%204%20are%20text%20values%20like%20this%20%3A%20%227%2F26%2F2020%2C%204%3A04%3A19%20PM%22.%20Removing%20the%20comma%20between%20the%20date%20and%20time%20made%20Excel%20recognize%20them%20as%20dates.%20Perform%20a%20find%20and%20replace%20for%20%22%2C%20%22%20with%20%22%20%22%20and%20that%20should%20fix%20the%20issue.%20The%20conditional%20format%20will%20work%20after%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1547425%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20Date%2FTime%20in%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547425%22%20slang%3D%22en-US%22%3EYou%20are%20correct.%20They%20are%20text%20that%20is%20being%20pasted%20into%20the%20numbered%20sheets%20from%20a%20data%20source.%20The%20summary%20page%20pulls%20that%20information%20from%20the%20other%20sheets%20so%20each%20cell%20in%20the%20summary%20is%20actually%20a%20link%20to%20another%20cell%20in%20a%20seperate%20sheet.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20order%20for%20this%20to%20work%20the%20way%20it%20needs%20to%2C%20finding%20and%20replacing%20the%20comma%20each%20time%20is%20not%20feasable.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20another%20way%20to%20get%20it%20to%20recognize%20this%20as%20a%20date%2Ftime%20format%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549157%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20Date%2FTime%20in%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740437%22%20target%3D%22_blank%22%3E%40Flambert86%3C%2FA%3E%26nbsp%3B%2C%20replace%20the%20link%20in%20cell%20H2%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D'1'!%24A%2417%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewith%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DDATEVALUE(SUBSTITUTE('1'!%24A%2417%2C%22%2C%22%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546959%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20Date%2FTime%20in%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20copy%20of%20the%20summary%20sheet%20and%204%20of%20the%20sheets%20that%20the%20summary%20draws%20data%20from.%20Thank%20you%20very%20much%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546932%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20Date%2FTime%20in%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546932%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20the%20quick%20response.%20Unfortunately%20it%20did%20not%20work.%20Any%20other%20thoughts%3F%20I%20attached%20a%20new%20photo%20of%20the%20formula%20you%20suggested.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546888%22%20slang%3D%22en-US%22%3EConditional%20formatting%20Date%2FTime%20in%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546888%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20sheet%20that%20pulls%20timestamp%20data%20from%20multiple%20sheets.%20I%20want%20to%20use%20conditional%20formatting%20to%20tell%20me%20if%20the%20timestamp%20is%20older%20than%2030%20minutes.%20The%20timestamp%20contains%20date%20and%20time%20information%20in%20the%20same%20cell%20with%20a%20format%20of%20%22m%2Fd%2Fyyyy%2C%20h%3Amm%3Ass%20AM%2FPM%22%20.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20cannot%20figure%20out%20what%20formula%20to%20use%20to%20find%20dates%2Ftimes%20older%20than%2030%20minutes.%20Currently%20the%20sheet%20uses%20conditional%20formatting%20for%20unique%20entries%20but%20it%20has%20some%20errors.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20attached%20a%20photo%20of%20the%20summary%20data.%20This%20is%20what%20it%20should%20look%20like%20when%20properly%20formatted.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20any%20help%20you%20can%20provide!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1546888%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

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!

7 Replies

@Flambert86 , try this in the conditional formatting:

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

 

TheAntony_0-1595809940840.png

 

 

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!

@Flambert86 , can you share a sample of the workbook without any confidential info?

@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!

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

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?
Best Response confirmed by Flambert86 (Occasional Contributor)
Solution

@Flambert86 , replace the link in cell H2:

='1'!$A$17

 

with:

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