Compare Now() to a European date

[Lars]

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

 

[Bernard Liengme]

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

 

[Gord Dibben]

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

 

[Lars]

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

 

[Lars]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Dave Peterson]

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.

 

[ShaneDevenshire]

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.

 

[Mais qui est Paul]

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

 

[Lars]

Bonsour® Lars

Bonsour Paul,

I am afraid Excel did not accept that as a valid formula at all. And
it pointed towards the commas (,), so I replaced them with ; and then
it was accepted, but did not produce any color according to my
conditional format settings.

I already had 20080606 in that cell, formatted as "general". What is
even worse though is that when I entered another date, on the same
format, into that cell, all I get in return is that dreaded ##### and
the message that negative dates are displayed as #####.

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

I have also tried the other suggestions given here yesterday by Bob
Phillips. It does not do anything at all as far as I can see. I must
admit that I understand little of the VBA code, but I am quite used to
debugging macros by stepping through them in the VBA-editor.

Maybe I should have mentioned already from the beginning that I have
XP SP2, english, and Office 2003 SP3, english. My regional options
though are set for Sweden and Swedish.

Lars
Stockholm

 

[Mais qui est Paul]

Hej Lars gärna understryka min du berätta för oss:

I am afraid Excel did not accept that as a valid formula at all. And
it pointed towards the commas (,), so I replaced them with ; and then
it was accepted, but did not produce any color according to my
conditional format settings.

I already had 20080606 in that cell, formatted as "general". What is
even worse though is that when I entered another date, on the same
format, into that cell, all I get in return is that dreaded ##### and
the message that negative dates are displayed as #####.

XP SP2, english, and Office 2003 SP3, english. My regional options
though are set for Sweden and Swedish.

Perhaps you probably have to type the formula in swedish :
Kanske har du förmodligen att skriva formeln på svenska:
Välj "formel" i den första rullgardinsmenyn och skriv in formeln
=IDAG()>DATUM(HELTAL(A1/10000),3+REST(HELTAL(A1/100),100),REST(A1,100))
Klicka på Format-knappen för att tillämpa dina specifika formatering

Have a look at this sample :
Ta en titt på detta prov:

No Macro, No VBA
http://cjoint.com/?fAsD4yRS5p

 

[Bob Phillips]

Did you put the code in the worksheet code module as directed? Did you put
the dates in H1:H10 or change the range in the macro?

--
---
HTH

Bob


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

 

[Lars]

Did you put the code in the worksheet code module as directed? Did you put
the dates in H1:H10 or change the range in the macro?

Yes I did. I have my dates in the B column and I did change the code
to B2:B20.

My news reader had chopped up your message so I had to reformat it.
Maybe I messed something up then.

Will this code run whenver there is a change in a cell in the
specified range?

Lars
Stockholm

 

[Bob Phillips]

Yes that is the idea.

If you input a date in the format yyyymmdd, it will change it to a real date
(as Excel knows it) and reformat it back, then apply the CF. If you input a
real date (again as Excel knows it), or change an existing date, it just
reformats it and applies the CF.


--
---
HTH

Bob


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

 

[Bob Phillips]

I have posted an example workbook at http://cjoint.com/?fBkU1guoMG

--
---
HTH

Bob


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

 

[Mais qui est Paul]

Bonsour® Bob Phillips avec ferveur ;o))) vous nous disiez :

I have posted an example workbook at http://cjoint.com/?fBkU1guoMG

Sorry, but what about Lars says :
Look at my last post...

 

[Lars]

Hej Lars gärna understryka min du berätta för oss:

Perhaps you probably have to type the formula in swedish :

No, my Office and Excel is in English.

=TODAY() > DATE(INT(A1 / 10000) [,] 3 + MOD(INT(A1 / 100) , 100) ,
MOD(A1 , 100))

Excel stops with a mark on the comma I put in brackets, [,] and it
says that "the formula contains an error".

_If_ I try, just for testing, to paste that formula as a function for
a cell, then again Excel indicates an error and has the first comma
marked. _If_ I replace that comma with a semicolon ( then Excel
moves on to the next comma and marks that in error.
When I replace all commas with semicolons Excel will accept the
formula but complain about it being a circular reference.

When I change Windows regional settings to US Excel will accept the
commas, so there is obviously some regional specifics with Sweden that
gets in the way.

However;
With Windows thinking I am in the US and I apply your suggestions for
conditional formatting, Excel will at first apply the color I have
chosen for that cell. When I fill in a date 4 months ahead of time it
does, correctly, not apply any formatting. But neither does it when I
move time ahead to just a week before, and update cells.

I take it that your formula is checking to see if my "now()" is larger
than the 'date' I have in the specific cell?


Lars
Stockholm

 

[Bob Phillips]

What about it?

--
---
HTH

Bob


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



Bonsour® Bob Phillips avec ferveur ;o))) vous nous disiez :

I have posted an example workbook at http://cjoint.com/?fBkU1guoMG

Sorry, but what about Lars says :
Look at my last post...

 

[Mais qui est Paul]

Hi Lars :

However;
With Windows thinking I am in the US and I apply your suggestions for
conditional formatting, Excel will at first apply the color I have
chosen for that cell. When I fill in a date 4 months ahead of time it
does, correctly, not apply any formatting. But neither does it when I
move time ahead to just a week before, and update cells.

Sorry, with my french Excel 2002, I am not able to reproduce the problems separator mentioned ...

Rereading the original specifications, here is an implementation closer to what was desired.
http://cjoint.com/?fBozFkdNhF

Greetings