Compare Now() to a European date

  • Thread starter Thread starter Lars
  • Start date Start date
Sorry, with my french Excel 2002, I am not able to reproduce the problems separator mentioned ...
Click to expand...

No I understand. It is obviosly my regional settings that does not
allow Excel to behave as I desire.

Have a look here at some screendumps I did; http://tinyurl.com/5h8a5b

I would think there must be a way to modify my settings without having
to play entirely under a different region.

I am not so surprised when Swedish settings vary quite a lot from
North Americans. We do express times and dates in a different way.
It surprises me more that there is a similar a difference to French.

Lars
Stockholm
 
Bonsour® Lars avec ferveur ;o))) vous nous disiez :
No I understand. It is obviosly my regional settings that does not
allow Excel to behave as I desire.
Click to expand...

AFAK and according to that in EXCEL worksheets, formulas are coded as token,
exchange with foreign users should be absolutely transparent ...

However, formats and definitions do not seem also to be translated :-(
nor conditional formulas , I suppose :-(

Ensure you in my example that column A is general format
i.e 20080527 is a true number not an Excel date coding like 39595 (May 27 2008)

and conditionnal formulas must be typed in english.

I am sorry for not being able to help you more !

Good Luck
 
Lars, did you check my last post?

--
---
HTH

Bob


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



Bonsour® Lars avec ferveur ;o))) vous nous disiez :
No I understand. It is obviosly my regional settings that does not
allow Excel to behave as I desire.
Click to expand...

AFAK and according to that in EXCEL worksheets, formulas are coded as token,
exchange with foreign users should be absolutely transparent ...

However, formats and definitions do not seem also to be translated :-(
nor conditional formulas , I suppose :-(

Ensure you in my example that column A is general format
i.e 20080527 is a true number not an Excel date coding like 39595 (May 27
2008)

and conditionnal formulas must be typed in english.

I am sorry for not being able to help you more !

Good Luck
 
Previously said:
I have posted an example workbook at http://cjoint.com/?fBkU1guoMG
Click to expand...

Yes I tried that one too, thank you.

When I open the sheet it looks fine. But when I reenter the very same
figures you have, or any other similar looking, Excel gives me the
##### and the message about negative dates.

I played around with my regional settings and get the same behaviour
regardless if I set my region to France, Sweden or USA.

Here is a screendump; http://tinyurl.com/3qwpjf

Lars
Stockholm
 
Lars,

Try this small adjustment

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B20"

On Error GoTo ws_exit
Application.EnableEvents = False

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

If Not IsDate(.Value2) Then

.Value = DateSerial(Left$(.Value2, 4), Mid$(.Value2, 5, 2),
Right$(.Value2, 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)
 
Now we are really getting somewhere!

If I apply the formulas for conditional formatting that you gave
previously;
=AND(B4>=TODAY();B4<=DATE(YEAR(TODAY());MONTH(TODAY())+1;DAY(TODAY())))
and
=AND(B4>=TODAY();B4<=DATE(YEAR(TODAY());MONTH(TODAY())+3;DAY(TODAY())))
cells do indeed change formatting to colors I have chosen. And I can
fill in new similar looking dates in cells, like 20080530, without
Excel kicking back with #####.

But, when I do fill in a date such as I use, all conditional
formatting goes away! There is no longer any conditional formatting at
all left for that cell.

If I fill something other than a date, like "Bob", formatting remains
intact. So it seems that when your VBA code is triggered it also
erases the CF.

If I have a 'date' such as 20080707 in a cell, say B4, and construct a
new CF with your formulas then B4 will take on the format I want it
to. But if I rather use the "format painter" from another cell on B4
then it will lose its CF, and jump almost 2000 years into the future,
to 3963-07-06.

Time travel?

Thank you for taking the time!

Previously said:
Try this small adjustment

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B20"

On Error GoTo ws_exit
Application.EnableEvents = False

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

If Not IsDate(.Value2) Then

.Value = DateSerial(Left$(.Value2, 4), Mid$(.Value2, 5, 2),
Right$(.Value2, 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
Click to expand...
Lars
Stockholm
 
Lars,

Here is another slight adjustment.

One thing to note is that the code applies the CF, so there is no need for
you to apply a copied format (although this code seems to handle that well
regardless).


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B20"
Dim currDate As Date

On Error GoTo ws_exit
Application.EnableEvents = False

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

On Error Resume Next
currDate = .Value

If Err.Number <> 0 Then
.Value = DateSerial(Left$(.Value2, 4), Mid$(.Value2, 5, 2),
Right$(.Value2, 2))
End If
On Error GoTo ws_exit
.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)
 
Previously said:
Here is another slight adjustment.

One thing to note is that the code applies the CF, so there is no need for
you to apply a copied format (although this code seems to handle that well
regardless).
Click to expand...

I am afraid nothing happens as to coloring any cells, regardless of
what I enter into them.

Lars
Stockholm
 
Lars,

No I am not American, I am English, so I too suffer Excel's American date
bias.

I am having a thought about this, so can you try this while I do some more
research

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B20"
Dim currDate As Date

On Error GoTo ws_exit
Application.EnableEvents = False

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

On Error Resume Next
currDate = .Value

If Err.Number <> 0 Then
.Value = DateSerial(Left$(.Value2, 4), Mid$(.Value2, 5, 2),
Right$(.Value2, 2))
End If
On Error GoTo ws_exit
.NumberFormat = "yyyymmdd"
.FormatConditions.Delete
.Range("IV1").Formula = "=AND(" & .Address(False, False) & _
">=TODAY()," & .Address(False, False) & _
"<=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY())))"
.FormatConditions.Add Type:=xlExpression, _
Formula1:=.Range("IV1").Formula
.Range("IV1").Formula = "=AND(" & .Address(False, False) & _
">=TODAY()," & .Address(False, False) & _
"<=DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())))"
.FormatConditions.Add Type:=xlExpression, _
Formula1:=.Range("IV1").Formula
.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)
 
If that fails, and I suspect it will, here is another version, and I think
this might just be it. What I cannot test is French Excel with France
regional settings.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B20"
Dim currDate As Date

On Error GoTo ws_exit
Application.EnableEvents = False

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

On Error Resume Next
currDate = .Value

If Err.Number <> 0 Then
.Value = DateSerial(Left$(.Value2, 4), Mid$(.Value2, 5, 2),
Right$(.Value2, 2))
End If
On Error GoTo ws_exit
.NumberFormat = "yyyymmdd"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(" & .Address(False, False)
& _
">=" & CLng(Date) &
Application.International(xlListSeparator) & .Address(False, False) & _
"<=" & CLng(DateSerial(Year(Date),
Month(Date) + 1, Day(Date))) & ")"
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(" & .Address(False, False)
& _
">=" & CLng(Date) &
Application.International(xlListSeparator) & .Address(False, False) & _
"<=" & CLng(DateSerial(Year(Date),
Month(Date) + 3, Day(Date))) & ")"
.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)
 
This version also seems to work and is more self-documentary IMO

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B20"
Dim currDate As Date
Dim sFormula As String

On Error GoTo ws_exit
Application.EnableEvents = False

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

On Error Resume Next
currDate = .Value

If Err.Number <> 0 Then
.Value = DateSerial(Left$(.Value2, 4), Mid$(.Value2, 5, 2),
Right$(.Value2, 2))
End If
On Error GoTo ws_exit
.NumberFormat = "yyyymmdd"
.FormatConditions.Delete
sFormula = "=AND(" & .Address(False, False) & _
">=TODAY()<,>" & .Address(False, False) & _
"<=DATE(YEAR(TODAY())<,>MONTH(TODAY())+1<,>DAY(TODAY())))"
.FormatConditions.Add Type:=xlExpression, _
Formula1:=Replace(sFormula, "<,>",
Application.International(xlListSeparator))
sFormula = "=AND(" & .Address(False, False) & _
">=TODAY()<,>" & .Address(False, False) & _
"<=DATE(YEAR(TODAY())<,>MONTH(TODAY())+3<,>DAY(TODAY())))"
.FormatConditions.Add Type:=xlExpression, _
Formula1:=Replace(sFormula, "<,>",
Application.International(xlListSeparator))
.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)
 
Previously said:
This version also seems to work and is more self-documentary IMO
Click to expand...

Again thank you for your persistence. The latest two versions you
posted both works quite well. But there are inconsistencies.

The top cell (B:2) never takes on any color regardless of what I
enter.

Occasionally some other cells too behave strangely. I enter 20080811
in B:10, it becomes yellow, as it should. I enter 20080911 in B:11 and
that turns yellow too, which it should not.

I enter 20080611 in B:12 and it remains without color.
Some times a cell just below the one I change will change color.

Could it be that the whole code does not always run through?

Lars
Stockholm
 
Lars,

I don't see any of those inconsistencies, everything colours as it should.

I started by leaving my setting to UK and it worked fine. I changed them to
French(France), and it still worked fine.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Actually, I may have sent you off in an entirely wrong direction.

There would be little point in going to all these lenghts to have
Excel tell me if the very I date I have just entered is within the
next three months.

The whole idea is that I enter the expiry dates of certificates when I
have just been given a new. And _then_ once in a while, I can trigger
Excel to alert me with colors that an expiry is on the horizon.
................

I skipper a small passenger boat doing day trips in the archipelago. I
have since years an Excelsheet with my certificates. There are almost
20 of them. Some need to be renewed annually, others every tvo years,
three years, five and ten. The five year one needs an intermediary
inspection at half time, which is 2½ years.
I have a macro that sorts the certificates such that the ones with
nearest expiry date goes to the top of the sheet. I have been fine
with this myself.

But a couple of weeks ago a colleague of mine had missed an expiry
date and was hassled by authorities for it. So I mentioned my sheet
to my boss, and he asked for a copy. So I made one sheet for each of
five boats, all in one book. That is when I came up with the idea of
color coding some dates.

Alas this Excel book needs to be idiot proof.
_I did not say that about my boss!_

Someone should be able to enter a date and some 4 years and 9 months
later find that a cell has turned yellow because it is getting near to
expiry. That is the idea.

Lars
Stockholm
 
Lars,

That is exactly what conditional formatting does. Because it is working off
of today's date it will flag items due within 2-3 months as yellow, items
within a month as red. It just so happens we mainly used nearby dates. But
the 20080911 will flag yellow later this month without you doing anything.
The CF is added when you input the date, but it stays there, it is not
temporary.

So does the date input work as you want it? Does

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Previously said:
I don't see any of those inconsistencies, everything colours as it should.

I started by leaving my setting to UK and it worked fine. I changed them to
French(France), and it still worked fine.
Click to expand...

Look at this picture. It is two screen dumps from only a couple of
minutes apart, that I have stitched together.

http://w1.844.telia.com/~u84408784/Excel\Excel_6.png

To the left, in the third cell from top, B:4, we can see how there is
no color at all even though there should be.

But when I enter a date into B:3 then B:2 changes colour.
Unfortunately B:3 does not take on any colouring.

We can also see on B:14 and 15, the dates 0725 and 0811, both within
three months of today does not get coloured.


Lars
Stockholm
 
Previously said:
But the 20080911 will flag yellow later this month without you doing anything.
The CF is added when you input the date, but it stays there, it is not
temporary.
Click to expand...

Good! I tried manipulating my current date in Windows to see if that
would trigger any changes, but it did not. Maybe I had needed to
restart the computer in between, or something.
So does the date input work as you want it?
Click to expand...

Not entirely. I frankly can not understand the inconsistencies. They
are there, as I mentioned earlier, even when I set my region to UK.

Is it possible to step through your code, to study it as it runs row
by row?


Lars
Stockholm
 
Back
Top