Excel 'end of month' datedif and Conditional formatting

[jedwar]

Help please!

I've created a spreadsheet with a cell containing today's date.

I then have another cell where I want a 'use-by-date' of a product (mm/yyyy)

1. I want to conditionally format the 'use-by-date' cell to highlight in yellow when its within so many days from today's date?


2. If the product is used (the mm/yyyy deleted) the cell will highlight in red until a replacement product's 'use by date' is entered.


I appreciate excel may cause me a few problems recognising the end-of-month date and hence I understand the zero of the month trick but I'm really head scratching on how to incorporate this into an entire formula for the cell.

Can anyone offer help or advice?

Thank you in advance <!-- google_ad_section_end --><!-- / message -->

 

[Aladin Akyurek]

Help please!

I've created a spreadsheet with a cell containing today's date.

I then have another cell where I want a 'use-by-date' of a product (mm/yyyy)

1. I want to conditionally format the 'use-by-date' cell to highlight in yellow when its within so many days from today's date?


2. If the product is used (the mm/yyyy deleted) the cell will highlight in red until a replacement product's 'use by date' is entered.


I appreciate excel may cause me a few problems recognising the end-of-month date and hence I understand the zero of the month trick but I'm really head scratching on how to incorporate this into an entire formula for the cell.

Can anyone offer help or advice?

Thank you in advance <!-- google_ad_section_end --><!-- / message -->

A2:

=TODAY()

C2 houses a use-by-date value, which is a date.

Invoke the following formulas in conditional formatting, applied to C2...

Condition 1:

=$C2=""

Color formatting: Red

Condition 2:

=ISNUMBER($C2)*($C2-$A$2<=10)

Color formatting: Yellow

Adjust the cells to suit as well as 10 (the "so many days" value).

 

[fromspenny]

Excel Workbook
	A	B	C	D	E
1	Todays Date		Use-by-Date		Days past today
2	12/06/2011		20/06/2011		8
3
4	Condition 1
5	=$C2=""
6
7	Condition 2
8	=ISNUMBER($C2)*($C2-$A$2
Sheet

 

[jedwar]

Hi both

Thanks for the assistance.

I've got it working with a set date but if I enter 00/07/2011 it won't conditionally format at all.

1. I'm led to believe that excel will recognize 00//07/2011 as 30/06/2011?

The reasoning being that my use-by-dates are set as mm/yyyy and thus to avoid having to know the number of days in 08/2015, I would merely enter 00/09/2015.



2. I have approx 150 cells to conditionally format with end-of-month dates, do I have to conditionally format each individually or is there any tricks I can employ?

Regards

Jon

 

[Aladin Akyurek]

Hi both

Thanks for the assistance.

I've got it working with a set date but if I enter 00/07/2011 it won't conditionally format at all.

1. I'm led to believe that excel will recognize 00//07/2011 as 30/06/2011?

If you type it yourself into a cell, Excel will take it as text, not as a number what a date is.

The reasoning being that my use-by-dates are set as mm/yyyy and thus to avoid having to know the number of days in 08/2015, I would merely enter 00/09/2015.[/quote]

A1: 8/1/2015

B1:

=EOMONTH(A1,0)


or

A1: 9/1/2015

B1:

=A1-1

might help.

2. I have approx 150 cells to conditionally format with end-of-month dates, do I have to conditionally format each individually or is there any tricks I can employ?

Regards

Jon

See above. This seems different from the task in your original post.