Conditional Format (underline) row of last date/time of each day in a range

**hanpete122** · 12-12-2016, 02:43 PM

Good day,

I am attempting to underline the row (A:D) that contains the last occurrence of a date/time in range of numerous chronologic dates/times. Essentially I just need to add visual aid to the spreadsheet to make the transition from one day to the next more clear. I have been able to do this (in the past) with only dates. But adding the time element has pushed me into deeper "excel" waters. For further detail, the range that I am working with is not raw data, rather it is populated via index-match array fx from a separate data entry sheet. See screenshot.

SOF.jpg

**JohnTopley** · 12-12-2016, 02:46 PM

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**etaf** · 12-12-2016, 03:02 PM

is this based on A or B or both ?

=NOT(INT($A2)=INT($A3))
should underline

but as suggested lets see an example

**hanpete122** · 12-12-2016, 03:39 PM

Thanks,

This is a pretty big file (over 5mb) so I can not attach. Am working to mock something up now. To answer your question is based on data in col-A only.

Peter H.

**etaf** · 12-12-2016, 03:41 PM

then
=NOT(INT($A2)=INT($A3))
if you select the range A2:A????

if you just want to select A
then
=NOT(INT($A1)=INT($A2))
BUT it will underline the header

**hanpete122** · 12-12-2016, 04:04 PM

etaf,

Your solution works perfectly. Would you mind explaining it to me?

Thanks very much

Peter H.

**etaf** · 12-12-2016, 04:11 PM

so the date is a number from 1/1/1900
if you right click on a cell and change format to general
you will see a number
today() 12th Dec is 42716
the time is the decimal part of the number
so 6:00 am is
0.25
1/4 of a 24hr day

so to see if a date changes - from one cell to the next , we just need to see if the date number are the same between the rows
BUT of course the times will not be
so INT()
gets rid of the time and so now we are just comparing the date

does the date value in cell A2 = Cell A3 if it does and the same date , move on

INT(A2)=INT(A3)

BUT conditional formatting is applies if TRUE
so if they are the same date it will be TRUE
if they are NOT the same date - then false , i need to change it now so that TRUE = FALSE and FALSE = TRUE
hence the
NOT()

Just thought, we could have done
INT(A2)<>INT(A3)

any way - thats how it works, if still dont follow - say which bit and i will try to explain again

**hanpete122** · 12-12-2016, 04:19 PM

I follow 100%, I'd heard of INT but never delved into or used before. Presume INT to be an abbreviation for integer. So in essence with INT you are "calling" only the INT data?

Peter H.

**etaf** · 12-12-2016, 04:26 PM

yes,INT() just takes the integer and removes the decimal
or we could use
TRUNC()
to do the same thing

to get just the decimal
then
cell - INT(Cell)

**hanpete122** · 12-12-2016, 04:30 PM

Outstanding!

Thanks for the lesson. This will be a really valuable tool.

Peter H.

**etaf** · 12-12-2016, 04:36 PM

you are welcome

Conditional Format (underline) row of last date/time of each day in a range

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Format (underline) row of last date/time of each day in a range

Re: Conditional Format (underline) row of last date/time of each day in a range

Re: Conditional Format (underline) row of last date/time of each day in a range

Re: Conditional Format (underline) row of last date/time of each day in a range

Re: Conditional Format (underline) row of last date/time of each day in a range

Re: Conditional Format (underline) row of last date/time of each day in a range

Re: Conditional Format (underline) row of last date/time of each day in a range

Re: Conditional Format (underline) row of last date/time of each day in a range

Re: Conditional Format (underline) row of last date/time of each day in a range

Re: Conditional Format (underline) row of last date/time of each day in a range

Re: Conditional Format (underline) row of last date/time of each day in a range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Conditional format cell colour if time between a range

[SOLVED] Conditional format if date falls in range

[SOLVED] Conditional format Date/time that is 16 hours greater or Less

Excel 2007 Conditional Format date range

[SOLVED] Sum Range of Cells with Date Time format

Excel 2007 : conditional format based on date range

Conditional format range based on date

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions