1

I made a script allowing to automatically add the stats of the week, but I have an issue to display the date of the Monday of the current week.

Given 2017-06-26, on my laptop I get 2017-06-26 which is correct, but on the other laptop I get 2017-06-03...

How to solve this? I tried to put the same language on both machines, but same problem. Furthermore, when I use TODAY and WEEKDAY independently, the values of the 2nd computer are the same as for the first computer, its only occurs when I want to calculate the date of the first day of the current week.

Here is the relevant code:

Range("A" & i + 5).Formula = "=TODAY()-WEEKDAY(TODAY(),2)+1"  '"week "[$-en-US]mmmm jj"th"
Range("A" & i + 5).NumberFormat = """week ""[$-en-US]mmmm dd""th"""
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Hugo Fischer
  • 11
  • 3
  • I already said it in my first message : First laptop : Result : 26 june Expectd : 26 june Second laptop : Result : 3 june Expected : 26 june Same code on both laptop – Hugo Fischer Jun 30 '17 at 08:01
  • I've edited your question to clarify, and retracted my votes. Note that I assumed the incorrect date value you are getting is `2017-06-03`; please confirm you meant "June 3rd" as such. – Mathieu Guindon Jun 30 '17 at 13:31
  • 1
    My result is "week June 03th", but only as a result of the `NumberFormat`. Without that, the date appears correctly. – David Zemens Jun 30 '17 at 13:34
  • If you look at the number format that's actually applied, you probably see something like: `"week "[$-F6EFC]mmmm dd"th"`. There seems to be something wrong with your format string. – David Zemens Jun 30 '17 at 13:38
  • 1
    If I use a format string as `"week "mmm dd"th"` then the results are `week June 26th`. – David Zemens Jun 30 '17 at 13:39
  • @DavidZemens correct. It's the `[$-en-US]` that's messing it up. `mmm` would output `Jun` though ;-) – Mathieu Guindon Jun 30 '17 at 13:41
  • 1
    If you're trying to force EN-US dates, use the *format code* [$409] instead of `[$-en-US]` https://stackoverflow.com/questions/894805/excel-number-format-what-is-409 – David Zemens Jun 30 '17 at 13:42
  • @DavidZemens looks like you have answer material there :) – Mathieu Guindon Jun 30 '17 at 13:44
  • @Mat'sMug probably just gonna dupe it – David Zemens Jun 30 '17 at 13:45
  • though I'm not sure *why* it's a problem on one machine but not another, I suspect it has to do with locale settings. – David Zemens Jun 30 '17 at 13:46
  • 1
    My understanding is that this is just a misuse of a formatting predicate; "if culture is en-US then format this way, otherwise.... (no alternative specified)" - pretty sure OP would get correct output all the time without it. @DavidZemens I already retracted a close vote, can't dupe-hammer - it's all yours! – Mathieu Guindon Jun 30 '17 at 13:50
  • Ahhhh @Mat'sMug so you're saying the discrepancy appears only *if* the user locale is en-US, but otherwise the formatting condition isn't applied? That would make sense then. – David Zemens Jun 30 '17 at 13:54
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/148063/discussion-between-mats-mug-and-david-zemens). – Mathieu Guindon Jun 30 '17 at 13:55

0 Answers0