Compare Now() to a European date

[Bob Phillips]

This workbook is not too helpful as it has lots of formulae such as
=1*TEXT(TODAY()+ROW(),"aaaammjj") in it, which just don't work in a
non-French version of Excel.

--
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 :

about inconsistence ...


dare I mention this latest proposal?
http://cjoint.com/?gbrlUPy4pu

 

[Bob Phillips]

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.

Probably need to restart Excel.

Actually, I just tried it, and all you need is to force a recalculation,
Ctrl-Alt-F9

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?

You mean me to step through my code? Not a lot of point as I am not getting
these inconsistencies.

 

[Mais qui est Paul]

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

This workbook is not too helpful as it has lots of formulae such as
=1*TEXT(TODAY()+ROW(),"aaaammjj") in it, which just don't work in a
non-French version of Excel.

Hello Bob,
;o))) you say :

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

bias.
I have already experienced the inconvenience with colleagues
Spanish ( "aaaammdd") and German ( "jjjjmmtt")

for this example : formulas in range A:A
are used only to perform automatically correct ISO date
all entry in this range may be erased

As I have already said in a previous response :
during the exchanges between foreign users : formats and string did not appear to be translated nor conditional formulas ...
:-(

I produced this example voluntarily without macro.
I'm sorry, but I can not circumvent these inconveniences without using some macros limited to this one event

Obviously it is possible to do :
1- some search & replace on a selected range :
Selection.Replace What:="""aaaammjj""", Replacement:="""yyyymmdd""", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

2 - for named cells
ActiveWorkbook.Names.Add Name:="Expiry7", RefersToR1C1:= _
"=1*TEXT(TODAY()+7,""yyyymmdd"")"
ActiveWorkbook.Names.Add Name:="ExpiryM", RefersToR1C1:= _
"=1*TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY())),""yyyymmdd"")"
ActiveWorkbook.Names.Add Name:="Expiry3M", RefersToR1C1:= _
"=1*TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),""yyyymmdd"")"

3 - for CF :
Range("A2:A100").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=Expiry7", Formula2:="=1*TEXT(today(),""yyyymmdd"")"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=ExpiryM", Formula2:="=Expiry7"
Selection.FormatConditions(2).Interior.ColorIndex = 45
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=Expiry3M", Formula2:="=ExpiryM"
Selection.FormatConditions(3).Interior.ColorIndex = 6

 

[Lars]

You mean me to step through my code? Not a lot of point as I am not getting
these inconsistencies.

No, ;-) I would like to step through it myself, to see if I can detect
the reason for the inconsistencies I experience.

I have many times stepped through macros and that way found where it
took a different turn from what I thought it would.

So I tried to step through your code in the VB editor but nothing
happens when I tell it to "step into".


Lars
Stockholm

 

[Bob Phillips]

What do you mean by '... nothing happens'? Did you set a break-point, and
step from that point on? If so, where?

--
---
HTH

Bob


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

 

[Lars]

What do you mean by '... nothing happens'? Did you set a break-point, and
step from that point on? If so, where?

No I did not set any break points.

With macros I can just start Excels "Step into" function and then step
through it with F8. It will move down one row at a time and I can
easily follow on the sheet what actually happens.

But I suppose it is different for a "change"-function.


Lars
Stockholm

 

[Bob Phillips]

Yes it is, you can't fire the macro.

You need to go to the VBIDE, select the worksheet module, and set a break
point, preferably at the first IF test, then goto the spreadsheet, make a
change, and then step through when you are passed over to the VBIDE again.

--
---
HTH

Bob


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