Count and Highlight date greater than 1 day

Hello,

Is there a way to count date between (Start Date , Finish Date ) > 1 to be highlighted ?

Could you show us what your expected results would be?

BSB

I can't view your attached image, but if your request is as straightforward as I'm interpreting it to be - you want to highlight all entries in which the finish date is more than a day after the start date - you should be able to select A2:B5 then use the formula below as your Conditional Formatting formula:

=$B2-$A2>1

cantosh,
I try It did not work, For example what I did is
as you explain in the image.

https://s3.postimg.org/5ez38vgv7/image.png

Try following the formula path instead of the 'highlight greater than' path. To clarify:

1. Select A2:B5
2. Click 'Conditional Formatting' --> 'New Rule'
3. Click 'Use a formula to determine which cells to format'
4. In the 'Format values where this formula is true' box, enter the following:
=$B2-$A2>1
5. Click 'Format', select the 'Fill' tab, pick your highlight color, and select 'OK'
6. 'OK' through the rest of the process

I've attached a sample if you need it. It's worth noting that the Nov. 31 date in your original sample does not exist as a date (30 days in Nov.), so Excel doesn't really know what to do with it.

Hope this helps!

If you want Column C to show the number of days between the Start Date and Finish Date then you need a formula like the one below in cell C2

=IF(A2<B2,DATEDIF(A2,B2,"D"),"Start date after Finish Date")

You can then drag this down the column. The IF statement just checks to make sure the Start Date is before the Finish date to avoid an error.

If you want to highlight values you would just do similar to what you did above but just enter the value 1 in the box - in this case it will highlight all rows though.

cantosh,

Thank you It worked

Andrew Crystal,
Yes I check it, as It shows total count of difference between two dates, I think this formula also work correct ? which is more easy

=DAYS360(A2,B2)
A2= Start Date
B2= End Date

but after finish you need at least sort them from filter and highlight them manually

Hi,

You need to be careful using the DAYS360 forumula as it is based on 360 days in a year not 365, and it also assumes that each month has 30 days - as far as I know it's mainly used in accounting and you can see more about it here - https://support.office.com/en-gb/art...f-0cbda5718c2a

It isn't something I've really used but suspect that it won't really work how you want it to.