This is driving me nuts,

I have a list of certificates. In column B their expiry dates are
entered as Europeans, some at least, do. Like today would be 20080524.

I want these cells to change colour with conditional formating.
For instance becoming yellow when there is less than three months
between now and the expiry date, and then becoming red when there
is less than one month to expiry. Else they should remain without
colour.

I have read through a hundred posts dealing with similar needs and
seemingly fine replies, but I get nowhere with my particular sheet.

When I format my B cell as "custom" yyyymmdd and enter my date
Excel says that negative dates are displayed as ##########.
When I leave its format as "general" the cells become red whatever
number I enter there.

I have tried having a now-function in a separate cell and comparing my
B's to it, but it does not even give a clue to where I am doing wrong.

Any ideas?


Lars
Stockholm

In Excel dates are stores as numbers starting from 1/1/1900
Only formatting changes what the show as on the worksheet
Sounds like you 20080524 is either text or just a number but not an Excel
date
If the entry 20000524 is text or number then
=DATE(--LEFT(A1,4),--MID(A1,5,2),--RIGHT(A1,2))
will give you a real date
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Lars" <Lars@fake.com> wrote in message
news:3cvg34p0ickrk11drjb61lcd6s7s27k9cd@4ax.com...
> This is driving me nuts,
>
> I have a list of certificates. In column B their expiry dates are
> entered as Europeans, some at least, do. Like today would be 20080524.
>
> I want these cells to change colour with conditional formating.
> For instance becoming yellow when there is less than three months
> between now and the expiry date, and then becoming red when there
> is less than one month to expiry. Else they should remain without
> colour.
>
> I have read through a hundred posts dealing with similar needs and
> seemingly fine replies, but I get nowhere with my particular sheet.
>
> When I format my B cell as "custom" yyyymmdd and enter my date
> Excel says that negative dates are displayed as ##########.
> When I leave its format as "general" the cells become red whatever
> number I enter there.
>
> I have tried having a now-function in a separate cell and comparing my
> B's to it, but it does not even give a clue to where I am doing wrong.
>
> Any ideas?
>
>
> Lars
> Stockholm

As an alternative to the formulas try Data>Text to Columns>Next>Next>Column Data
Format>Date>YMD

20000524 returns May 24, 2008


Gord Dibben MS Excel MVP

On Sat, 24 May 2008 20:25:53 -0300, "Bernard Liengme"
<bliengme@stfx.TRUENORTH.ca> wrote:

>In Excel dates are stores as numbers starting from 1/1/1900
>Only formatting changes what the show as on the worksheet
>Sounds like you 20080524 is either text or just a number but not an Excel
>date
>If the entry 20000524 is text or number then
>=DATE(--LEFT(A1,4),--MID(A1,5,2),--RIGHT(A1,2))
>will give you a real date
>best wishes

Previously, on Usenet Gord Dibben <gorddibbATshawDOTca> wrote:

>As an alternative to the formulas try Data>Text to Columns>Next>Next>Column Data
>Format>Date>YMD
>
>20000524 returns May 24, 2008

Which is exactly what I don't want it to. We do write dates as
20080524 because we think it is a better way. Our plan is to
continue doing it.

Our certificates are dated as YYYYMMDD, and since we are used
to see dates in that form I would rather not introduce another
format in a book that is all about bringing clarity to what and when.


Lars
Stockholm

Previously, on Usenet "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca>
wrote:

>In Excel dates are stores as numbers starting from 1/1/1900
>Only formatting changes what the show as on the worksheet
>Sounds like you 20080524 is either text or just a number but not an Excel
>date

No. I fill in the figures 20080524 in a cell, and it becomes a
"general" format. I can read it fine but not have Excel compare
it to a now().

When I do change the format of the cell where I have filled in
20080524 to "custom" YYYYMMDD, Excel sees it as a negative
date and shows ########

>If the entry 20000524 is text or number then
>=DATE(--LEFT(A1,4),--MID(A1,5,2),--RIGHT(A1,2))
>will give you a real date

Thank you, but on this side of the pond 20080524 is a real date.

It is not date I am after, but conditional formatting based on a date.

Lars
Stockholm

Lars,

You could have the date fields formatted as yyyymmdd, and if you enter them
in standard date format, dd/mm/yyyy, you will see them exactly as you want,
and the conditional formatting will work.

Alternatively, you could enter them as yyyymmdd, and have event code to
reformat them and show as you want. This code will do all that and even add
the CF

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If Not IsDate(.Value) Then

.Value = DateSerial(Left$(.Value, 4), Mid$(.Value, 5, 2),
Right$(.Value, 2))
End If
.NumberFormat = "yyyymmdd"
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(" & .Address(False, False)
& _
">=TODAY()," & .Address(False, False) & _
"<=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY() )))"
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(" & .Address(False, False)
& _
">=TODAY()," & .Address(False, False) & _
"<=DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY() )))"
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions(1).Interior.ColorIndex = 6
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Lars" <Lars@fake.com> wrote in message
news:r44i349qcoi9uif0bo3iej3m1d3avbs0p0@4ax.com...
> Previously, on Usenet "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca>
> wrote:
>
>>In Excel dates are stores as numbers starting from 1/1/1900
>>Only formatting changes what the show as on the worksheet
>>Sounds like you 20080524 is either text or just a number but not an Excel
>>date
>
> No. I fill in the figures 20080524 in a cell, and it becomes a
> "general" format. I can read it fine but not have Excel compare
> it to a now().
>
> When I do change the format of the cell where I have filled in
> 20080524 to "custom" YYYYMMDD, Excel sees it as a negative
> date and shows ########
>
>>If the entry 20000524 is text or number then
>>=DATE(--LEFT(A1,4),--MID(A1,5,2),--RIGHT(A1,2))
>>will give you a real date
>
> Thank you, but on this side of the pond 20080524 is a real date.
>
> It is not date I am after, but conditional formatting based on a date.
>
> Lars
> Stockholm

Couple of problems with my code, fixed here

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If Not IsDate(.Value) Then

.Value = DateSerial(Left$(.Value, 4), Mid$(.Value, 5, 2),
Right$(.Value, 2))
End If
.NumberFormat = "yyyymmdd"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(" & .Address(False, False)
& _
">=TODAY()," & .Address(False, False) & _
"<=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY() )))"
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(" & .Address(False, False)
& _
">=TODAY()," & .Address(False, False) & _
"<=DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY() )))"
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions(2).Interior.ColorIndex = 6
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Lars" <Lars@fake.com> wrote in message
news:r44i349qcoi9uif0bo3iej3m1d3avbs0p0@4ax.com...
> Previously, on Usenet "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca>
> wrote:
>
>>In Excel dates are stores as numbers starting from 1/1/1900
>>Only formatting changes what the show as on the worksheet
>>Sounds like you 20080524 is either text or just a number but not an Excel
>>date
>
> No. I fill in the figures 20080524 in a cell, and it becomes a
> "general" format. I can read it fine but not have Excel compare
> it to a now().
>
> When I do change the format of the cell where I have filled in
> 20080524 to "custom" YYYYMMDD, Excel sees it as a negative
> date and shows ########
>
>>If the entry 20000524 is text or number then
>>=DATE(--LEFT(A1,4),--MID(A1,5,2),--RIGHT(A1,2))
>>will give you a real date
>
> Thank you, but on this side of the pond 20080524 is a real date.
>
> It is not date I am after, but conditional formatting based on a date.
>
> Lars
> Stockholm

The value that the cell holds and what you see in the cell don't have to match.

Enter the date as a real date--match your windows short date settings.

But then format the cell to show yyyymmdd.

You'll see a difference between what's in the formula bar and what's displayed
in the cell.

Gord's suggestion was a way to convert those numbers (not really dates) to real
dates so that you could format them the way you want.

ps. It's not really showing a negative date (when you see ###'s). It's just
that 20080524 is out of the range of what dates excel supports.

In excel, dates are just counts from a base date (usually Dec 31, 1899 in the
wintel world). That 20080524 is 20,080,524 days since Dec 31, 1899.



Lars wrote:
>
> Previously, on Usenet Gord Dibben <gorddibbATshawDOTca> wrote:
>
> >As an alternative to the formulas try Data>Text to Columns>Next>Next>Column Data
> >Format>Date>YMD
> >
> >20000524 returns May 24, 2008
>
> Which is exactly what I don't want it to. We do write dates as
> 20080524 because we think it is a better way. Our plan is to
> continue doing it.
>
> Our certificates are dated as YYYYMMDD, and since we are used
> to see dates in that form I would rather not introduce another
> format in a book that is all about bringing clarity to what and when.
>
> Lars
> Stockholm

--

Dave Peterson

Hi Lars,

This looks like an ISO date issue. I have developed a file which
demonstrates ISO date conversions. In it there are spreadsheet formulas or
VBA functions. If you are interested contact me.

--
Cheers,
Shane Devenshire
shanedevenshire@sbcglobal.net

"Lars" wrote:

> This is driving me nuts,
>
> I have a list of certificates. In column B their expiry dates are
> entered as Europeans, some at least, do. Like today would be 20080524.
>
> I want these cells to change colour with conditional formating.
> For instance becoming yellow when there is less than three months
> between now and the expiry date, and then becoming red when there
> is less than one month to expiry. Else they should remain without
> colour.
>
> I have read through a hundred posts dealing with similar needs and
> seemingly fine replies, but I get nowhere with my particular sheet.
>
> When I format my B cell as "custom" yyyymmdd and enter my date
> Excel says that negative dates are displayed as ##########.
> When I leave its format as "general" the cells become red whatever
> number I enter there.
>
> I have tried having a now-function in a separate cell and comparing my
> B's to it, but it does not even give a clue to where I am doing wrong.
>
> Any ideas?
>
>
> Lars
> Stockholm
>

Bonsour® Lars avec ferveur ;o))) vous nous disiez :

> This is driving me nuts,
;o)))
> I have a list of certificates. In column B their expiry dates are
> entered as Europeans, some at least, do. Like today would be 20080524.
>
> I want these cells to change colour with conditional formating.
> For instance becoming yellow when there is less than three months
> between now and the expiry date, and then becoming red when there
> is less than one month to expiry. Else they should remain without
> colour.

I supppose column "B" format is Standard

Select (Format > Conditional Formatting) to display the Conditional Formatting dialog box.
Condition 1
Select "Formula Is" in the first drop-down box and enter the formula
=TODAY() < DATE(INT(B1 / 10000) , 3 + MOD(INT(B1 / 100) , 100) , MOD(B1 , 100))
Click the Format button to apply your specific formatting


HTH
--
@+
;o)))