VBA displays dates in US format for ‘dd/mm/yyyy’ when ‘dd’=1-12?

[pdebee]

Hello everyone,

[This is my very first thread, so please forgive me if I did anything wrong; thank you.]

My worksheet has a sequence of 'Tasks' listed in column 'A', the corresponding 'Start Date & Time' in column 'B' and the corresponding 'End Date & Time' in column 'C'.

The purpose of this worksheet is to track the time it takes for someone to begin and end each task in the sequence. There are subsequent columns which calculate the 'Duration' taken to complete each task, which is then broken down further into 'days', 'hours' and 'minutes', with 'Totals' at the bottom of the worksheet.

The 'Tasks' in column 'A' are described using simple text and the cells are therefore pre-formatted using the category of 'General'. All the 'Date & Time' cells are pre-formatted using the 'Custom' category and set to 'dd/mm/yyyy hh:mm:ss', and are also dynamically initialised to the current value of '=NOW()'.

When someone starts a task, s/he goes to the worksheet and presses the F2, F9 and Enter keys to record, into the cell, the static value of the then-current date and time. In order to simplify those key sequences further, I wrote my first ever, very simple <acronym title="visual basic for applications">VBA</acronym> macro, using the macro recorder first, and then modifying it by hand:

Sub UpdateTime()
'
' UpdateTime Macro
' Assign current date and time as static value in current cell.
'
' Keyboard Shortcut: Ctrl+Shift+T
'
ActiveCell.FormulaR1C1 = Format(Now, "dd/mm/yyyy hh:mm:ss")
End Sub

I wrote and tested this design on the 30^th of December 2014 and it worked perfectly: whenever I pressed Ctrl+Shift+T, the current date & time was inserted into the current cell in place of ‘=NOW()’ and in the format I had defined: "dd/mm/yyyy hh:mm:ss".

Imagine my surprise when, on the 3^rd of January 2015, the same process returned date values formatted US style: "01/03/2015 09:15:36", for example. Nothing else had changed: my Excel regional settings are still as they were before (‘dd/mm/yyyy’) and my computer clock is still displaying it that way, as ever before. Needless to say, I didn’t change anything at all in the worksheet between 30/12/2014 and 03/01/2015, since it was working as designed.

Another strange symptom was that, sometimes, the ‘seconds’ were dropped from the ‘time’ shown in the cell: "01/03/2015 09:21", for example.

I am suspecting that, because the first 12 days (‘dd/mm/yyyy’) of any month match the values of months in the US format (‘mm/dd/yyyy’), Excel’s VBA processing overrules my format settings.
The reason for suspecting the VBA’s own code is that I can still obtain the current date and time in the desired format of ‘dd/mm/yyyy’ when I use the original key sequences: F2, F9, Enter.

I am obviously waiting to run this again on the 13^th of January, to see if it makes any difference and thus to be able to prove/disprove my hypothesis.

In the meantime, could anyone offer any advice on how I could tweak my very simple macro to bypass this strange behaviour in VBA?

Thank you very much, in advance, for any pointers, bearing in mind that I am a real novice at coding VBA macros, and would therefore be very grateful if the experts among you would kindly update my subroutine, shown above, with code offering a watertight solution. Thanks a lot!

Regards;
Patrick.

 

[Rick Rothstein]

I'm in the US, so I cannot see the problem, but others have reported that VB always uses US centric dates. Try your code this way (letting Excel do the work)...

Sub UpdateTime()
  '
  ' UpdateTime Macro
  ' Assign current date and time as static value in current cell.
  '
  ' Keyboard Shortcut: Ctrl+Shift+T
  '
  ActiveCell.Value = Now
  ActiveCell.NumberFormat = "dd/mm/yyyy hh:mm:ss"
End Sub

 

[pdebee]

Dear Rick, )

Very many thanks for your extremely prompt response! I really appreciate it.

I have applied your suggested code and my macro now works perfectly! I am very grateful to you for taking the time to offer your help and sample code.

Thank you once again!
With kind regards;
Patrick.