Date format issue since 01/04/2015

vinnie01

New Member
Joined
Feb 18, 2015
Messages
34
Hello

I have some VBA code which formats a date field to a certain format. This was all working fine until 1st April 2015 !

Input date format = mm/dd/yyyy

Output date format = yyyy-mm-ddThh:mm:ss.SSS-05:00

my code..
oE.Range("N" & br).Value = Format(strDateTime, "yyyy-MM-dd") & Format(Now(), "Thh:mm:ss.SSS") & "-05:00"

Value of strDateTime =03/23/2015 formats OK as expected to 2015-03-23T09:44:02.022-05:00

Value of strDateTime =04/01/2015 format conversion is incorrect 2015-09-04T10:08:12.1212-05:00 (expected to be 2015-04-09T10:08:12.1212-05:00)


Appriciate any help on this

Cheers
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
What is your short date format in Windows Regional Settings? Your issue may be that the day and month are ambiguous for 04/01/2015 but not for 03/23/2015 because 23 is greater than 12.
 
Upvote 0
So why are you assigning a date with the format mm/dd/yyyy to the variable strDateTime? It needs to be in your short date format.
 
Upvote 0
nope - defo not a April fools joke. As I metioned no issue for the past few months - only since the start of April
 
Upvote 0
nope - defo not a April fools joke. As I metioned no issue for the past few months - only since the start of April
Well no one would have done it 'a few months' ago, so of course you had no issues a for the past few months.
That's the point of an April fools joke, it happens on the 1st day of April (not in January).

So lets ask it this way.

Under normal cercumstances, before this issue began..
If you type 4/7/2015 into a cell
And format that cell as mmm dd, yyyy
Do you expect that date to be Apr 07, 2015, or Jul 04, 2015 ?
 
Last edited:
Upvote 0
I would expect to see Jul 04, 2015 - since my short date format is set to dd/mm/yyyy. BTW this is the short date format on all laptops in the office - not just my specific one.
 
Upvote 0
I think the problem probably lies in how the date is assigned to the variable strDateTime
Can you show that code?

What does this show if the variables value is 04/01/2015
MsgBox Format(strDateTime,"mmm dd yyyy") ?
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top